﻿/*ESRPConnectionString*/
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
/*Modificari pana la data de 08.08.2010*/
/*
T:ValoriDimensiuni  + CodUnitate
T:Documente + [Dublare_Documente_Furnizor_Numar_Data]
T:RegimFiscal + [CuTva]

SP: +[fares_CautaArticole]
SP: +[fares_GenereazaJurnalCumparari] 
SP: +[fares_GenereazaJurnalVanzari]
SP: +-[GetBalanta]
SP: +-{InitConturiCoresp] - se adauga cont rezumat implicit
SP: +-fares_FiltreazaDocumente
*/


SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON


DECLARE @cmd varchar(8000)
IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetOrderString]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
SET @cmd ='CREATE FUNCTION [dbo].[GetOrderString] 
(
	@Cont TipCont	
)
RETURNS TipCont
AS
BEGIN
	DECLARE @Ret TipCont 
	SET @Ret=@Cont 
	IF CHARINDEX(''.'',@cont)<1 
		BEGIN 
		SET @Ret = REPLICATE(''0'', 40-LEN(@Cont)) + @Cont	
		END
	ELSE
		BEGIN
			DECLARE @pointPoz int
			DECLARE @bucCont TipCont
			--numara cate puncte are
			DECLARE @i int,@Cate int
			SET @i=1
			SET @Cate=0
			WHILE @i<=LEN(@Ret)
			BEGIN
				IF SUBSTRING(@Ret,@i,1)=''.''
					SET @Cate=@Cate+1
				SET @i=@i+1
			END
			SET @Cate = 40/(@Cate+1)
			SET @Ret = ''''	
			SET @pointPoz = CHARINDEX(''.'',@Cont)
			WHILE (@pointPoz < LEN(@Cont)) AND (@pointPoz>0)
			BEGIN
				SET @bucCont = REPLICATE(''0'',@Cate-@pointPoz+1) + SUBSTRING(@Cont,1,@pointPoz-1)
				SET @Cont=SUBSTRING(@Cont,@pointPoz+1,LEN(@Cont))
				SET @pointPoz = CHARINDEX(''.'',@Cont)
				SET @Ret = @Ret + @bucCont
			END
			
			SET @Ret = @Ret + REPLICATE(''0'',@Cate-LEN(@Cont)) + @Cont
		END

	

	-- Return the result of the function
	RETURN @Ret

END'
EXEC(@Cmd)
END
GO

IF  NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_admin_RoleInMenu_admin_ApplicationMenu]') AND parent_object_id = OBJECT_ID(N'[dbo].[admin_RoleInMenu]'))
BEGIN
	CREATE TABLE [dbo].[admin_RoleInMenu](
		[IDMeniuAplicatie] [int] NOT NULL,
		[RoleId] [uniqueidentifier] NOT NULL,
		[TipAccess] [varchar](50) NOT NULL,
	 CONSTRAINT [PK_admin_RoleInMenu] PRIMARY KEY CLUSTERED 
	(
		[IDMeniuAplicatie] ASC,
		[RoleId] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[admin_RoleInMenu]  WITH CHECK ADD  CONSTRAINT [FK_admin_RoleInMenu_admin_ApplicationMenu] FOREIGN KEY([IDMeniuAplicatie])
	REFERENCES [dbo].[admin_ApplicationMenu] ([IDMeniuAplicatie])
	ALTER TABLE [dbo].[admin_RoleInMenu] CHECK CONSTRAINT [FK_admin_RoleInMenu_admin_ApplicationMenu]
	ALTER TABLE [dbo].[admin_RoleInMenu]  WITH CHECK ADD  CONSTRAINT [FK_admin_RoleInMenu_aspnet_Roles] FOREIGN KEY([RoleId])
	REFERENCES [dbo].[aspnet_Roles] ([RoleId])
	ALTER TABLE [dbo].[admin_RoleInMenu] CHECK CONSTRAINT [FK_admin_RoleInMenu_aspnet_Roles]

END
GO

IF NOT  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Banci]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[Banci](
		[IdBanca] [smallint] IDENTITY(1,1) NOT NULL,
		[Denumire] [varchar](100) NOT NULL,
		[BIC] [varchar](4) NOT NULL,
	 CONSTRAINT [PK_Banci] PRIMARY KEY CLUSTERED 
	(
		[IdBanca] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]

	SET IDENTITY_INSERT [Banci] ON
	INSERT INTO [Banci] ([IdBanca],[Denumire],[BIC])VALUES(0,'Nedefinit','----')
	INSERT INTO [Banci] ([IdBanca],[Denumire],[BIC])VALUES(1,'ATE BANK ROMANIA S.A.','MIND')
	INSERT INTO [Banci] ([IdBanca],[Denumire],[BIC])VALUES(2,'BRD - Groupe Societe Generale S.A.','BRDE')
	INSERT INTO [Banci] ([IdBanca],[Denumire],[BIC])VALUES(3,'Banca Comerciala Romana S.A.','RNCB')
	INSERT INTO [Banci] ([IdBanca],[Denumire],[BIC])VALUES(4,'RAIFFEISEN BANK SA','RZBR')
	INSERT INTO [Banci] ([IdBanca],[Denumire],[BIC])VALUES(5,'UniCredit Tiriac Bank S.A.','UNCR')
	INSERT INTO [Banci] ([IdBanca],[Denumire],[BIC])VALUES(6,'BANK LEUMI ROMANIA S.A.','DAFB')
	INSERT INTO [Banci] ([IdBanca],[Denumire],[BIC])VALUES(7,'Bancpost S.A.','BPOS')
	INSERT INTO [Banci] ([IdBanca],[Denumire],[BIC])VALUES(8,'Banca Romaneasca S.A.','BRMA')
	INSERT INTO [Banci] ([IdBanca],[Denumire],[BIC])VALUES(9,'Trezorerie','TREZ')
	INSERT INTO [Banci] ([IdBanca],[Denumire],[BIC])VALUES(10,'RBS BANK (ROMANIA) S.A.','ABNA')
	INSERT INTO [Banci] ([IdBanca],[Denumire],[BIC])VALUES(11,'Banca Transilvania S.A.','BTRL')
	INSERT INTO [Banci] ([IdBanca],[Denumire],[BIC])VALUES(12,'CEC Bank','CECE')
	INSERT INTO [Banci] ([IdBanca],[Denumire],[BIC])VALUES(13,'ALPHA BANK ROMANIA','BUCU')
	INSERT INTO [Banci] ([IdBanca],[Denumire],[BIC])VALUES(14,'ATCPMB','ATCP')
	SET IDENTITY_INSERT [Banci] OFF
END
GO


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Documente' AND COLUMN_NAME = 'ValoareTotalaCuAdaos')
BEGIN
ALTER TABLE dbo.Documente ADD
	ValoareTotalaCuAdaos decimal(25,5) NOT NULL DEFAULT 0
END
GO


IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TipDocumentPlata]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[TipDocumentPlata](
		[IDTipPlata] [smallint] IDENTITY(1,1) NOT NULL,
		[Denumire] [varchar](50) NOT NULL,
	 CONSTRAINT [PK_TipDocumentPlata] PRIMARY KEY CLUSTERED 
	(
		[IDTipPlata] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
	SET IDENTITY_INSERT [tipdocumentplata] ON
	INSERT INTO [tipdocumentplata] ([IDTipPlata],[Denumire])VALUES(1,'Alte documente de plata')
	INSERT INTO [tipdocumentplata] ([IDTipPlata],[Denumire])VALUES(2,'Bilet la ordin')
	INSERT INTO [tipdocumentplata] ([IDTipPlata],[Denumire])VALUES(3,'CARD')
	INSERT INTO [tipdocumentplata] ([IDTipPlata],[Denumire])VALUES(4,'CEC')
	INSERT INTO [tipdocumentplata] ([IDTipPlata],[Denumire])VALUES(5,'Chitanta')
	INSERT INTO [tipdocumentplata] ([IDTipPlata],[Denumire])VALUES(6,'Compensare cu partener')
	INSERT INTO [tipdocumentplata] ([IDTipPlata],[Denumire])VALUES(7,'Ordin de plata')
	SET IDENTITY_INSERT [tipdocumentplata] OFF

END
GO


IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DocumentePlata]') AND type in (N'U'))
BEGIN

	CREATE TABLE [dbo].[DocumentePlata](
		[IDDocumentPlata] [int] IDENTITY(1,1) NOT NULL,
		[Numar] [varchar](20) NOT NULL,
		[Data] [datetime] NOT NULL,
		[IDTipPlata] [smallint] NOT NULL,
		[ValoareTotala] [decimal](18, 2) NOT NULL,
		[IDPartener] [bigint] NOT NULL,
		[Reprezentand] [varchar](250) NULL,
		[TipOperatiune] [varchar](10) NOT NULL,
		[CodUnitate] [smallint] NOT NULL,
	 CONSTRAINT [PK_DocumentePlata] PRIMARY KEY CLUSTERED 
	(
		[IDDocumentPlata] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[DocumentePlata]  WITH CHECK ADD  CONSTRAINT [FK_DocumentePlata_Parteneri] FOREIGN KEY([IDPartener])
	REFERENCES [dbo].[Parteneri] ([IDPartener])

	ALTER TABLE [dbo].[DocumentePlata] CHECK CONSTRAINT [FK_DocumentePlata_Parteneri]

	ALTER TABLE [dbo].[DocumentePlata]  WITH CHECK ADD  CONSTRAINT [FK_DocumentePlata_TipDocumentPlata] FOREIGN KEY([IDTipPlata])
	REFERENCES [dbo].[TipDocumentPlata] ([IDTipPlata])

	ALTER TABLE [dbo].[DocumentePlata] CHECK CONSTRAINT [FK_DocumentePlata_TipDocumentPlata]

	ALTER TABLE [dbo].[DocumentePlata]  WITH CHECK ADD  CONSTRAINT [FK_DocumentePlata_Unitati] FOREIGN KEY([CodUnitate])
	REFERENCES [dbo].[Unitati] ([CodUnitate])


	ALTER TABLE [dbo].[DocumentePlata] CHECK CONSTRAINT [FK_DocumentePlata_Unitati]


	ALTER TABLE [dbo].[DocumentePlata] ADD  CONSTRAINT [DF_DocumentePlata_Valoare]  DEFAULT ((0)) FOR [ValoareTotala]


	ALTER TABLE [dbo].[DocumentePlata] ADD  CONSTRAINT [DF_DocumentePlata_TipOperatiune]  DEFAULT ('INC') FOR [TipOperatiune]

	ALTER TABLE [dbo].[DocumentePlata] ADD  CONSTRAINT [DF_DocumentePlata_CodUnitate]  DEFAULT ((0)) FOR [CodUnitate]
END
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DocumentePlataDetaliu]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[DocumentePlataDetaliu](
		[IDDocPlataDetaliu] [int] IDENTITY(1,1) NOT NULL,
		[IDDocument] [bigint] NULL,
		[IDDocumentPlata] [int] NOT NULL,
		[Valoare] [decimal](18, 2) NOT NULL,
	 CONSTRAINT [PK_DocumentePlataDetaliu] PRIMARY KEY CLUSTERED 
	(
		[IDDocPlataDetaliu] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[DocumentePlataDetaliu]  WITH CHECK ADD  CONSTRAINT [FK_DocumentePlataDetaliu_DocumentePlata] FOREIGN KEY([IDDocumentPlata])
	REFERENCES [dbo].[DocumentePlata] ([IDDocumentPlata])

	ALTER TABLE [dbo].[DocumentePlataDetaliu] CHECK CONSTRAINT [FK_DocumentePlataDetaliu_DocumentePlata]

	ALTER TABLE [dbo].[DocumentePlataDetaliu]  WITH CHECK ADD  CONSTRAINT [FK_DocumentePlataDetaliu_DocumentePlataDetaliu] FOREIGN KEY([IDDocument])
	REFERENCES [dbo].[Documente] ([IDDocument])

	ALTER TABLE [dbo].[DocumentePlataDetaliu] CHECK CONSTRAINT [FK_DocumentePlataDetaliu_DocumentePlataDetaliu]

	ALTER TABLE [dbo].[DocumentePlataDetaliu] ADD  CONSTRAINT [DF_DocumentePlataDetaliu_Valoare]  DEFAULT ((0)) FOR [Valoare]
END
GO

IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TipDimensiune]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[TipDimensiune](
		[IDTipDimensiune] [smallint] IDENTITY(0,1) NOT NULL,
		[Denumire] [varchar](150) NOT NULL,
		[Format] [varchar](10) NOT NULL,
		[Vizibila] [bit] NOT NULL,
	 CONSTRAINT [PK_TipDimensiune] PRIMARY KEY CLUSTERED 
	(
		[IDTipDimensiune] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
	 CONSTRAINT [IX_TipDimensiune] UNIQUE NONCLUSTERED 
	(
		[Denumire] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[TipDimensiune] ADD  CONSTRAINT [DF_TipDimensiune_Denumire]  DEFAULT ('') FOR [Denumire]

	ALTER TABLE [dbo].[TipDimensiune] ADD  CONSTRAINT [DF_TipDimensiune_Format]  DEFAULT ('') FOR [Format]

	ALTER TABLE [dbo].[TipDimensiune] ADD  CONSTRAINT [DF_TipDimensiune_Vizibila]  DEFAULT ((0)) FOR [Vizibila]

END
GO

IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ValoriDimensiuni]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[ValoriDimensiuni](
		[IDValoareDimensiune] [bigint] IDENTITY(1,1) NOT NULL,
		[IDTipDimensiune] [smallint] NOT NULL,
		[Tabela] [varchar](100) NOT NULL,
		[IDCheieTabela] [bigint] NOT NULL,
		[Valoare] [varchar](250) NOT NULL,
		[CodUnitate] [smallint] NOT NULL,
	 CONSTRAINT [PK_ValoriDimensiuni] PRIMARY KEY CLUSTERED 
	(
		[IDValoareDimensiune] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
	 CONSTRAINT [IX_ValoriDimensiuni] UNIQUE NONCLUSTERED 
	(
		[IDTipDimensiune] ASC,
		[Tabela] ASC,
		[IDCheieTabela] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]



	ALTER TABLE [dbo].[ValoriDimensiuni]  WITH CHECK ADD  CONSTRAINT [FK_ValoriDimensiuni_TipDimensiune] FOREIGN KEY([IDTipDimensiune])
	REFERENCES [dbo].[TipDimensiune] ([IDTipDimensiune])

	ALTER TABLE [dbo].[ValoriDimensiuni] CHECK CONSTRAINT [FK_ValoriDimensiuni_TipDimensiune]

	ALTER TABLE [dbo].[ValoriDimensiuni]  WITH CHECK ADD  CONSTRAINT [FK_ValoriDimensiuni_Unitati] FOREIGN KEY([CodUnitate])
	REFERENCES [dbo].[Unitati] ([CodUnitate])
	ALTER TABLE [dbo].[ValoriDimensiuni] CHECK CONSTRAINT [FK_ValoriDimensiuni_Unitati]

	ALTER TABLE [dbo].[ValoriDimensiuni] ADD  CONSTRAINT [DF_ValoriDimensiuni_CodUnitate]  DEFAULT ((0)) FOR [CodUnitate]

END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ValoriDimensiuni' AND COLUMN_NAME = 'CodUnitate')
BEGIN
	ALTER TABLE dbo.ValoriDimensiuni ADD
		CodUnitate smallint NOT NULL DEFAULT 0

	ALTER TABLE dbo.ValoriDimensiuni ADD CONSTRAINT
		FK_ValoriDimensiuni_Unitati FOREIGN KEY
		(
		CodUnitate
		) REFERENCES dbo.Unitati
		(
		CodUnitate
		) ON UPDATE  NO ACTION 
		 ON DELETE  NO ACTION 
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_CautaArticole]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_CautaArticole]
GO

CREATE PROCEDURE [dbo].[fares_CautaArticole]
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint,
	@CodArticol varchar(50),
	@Denumire varchar(100),
	@ContArticol TipCont,
	@ContCheltuiala TipCont
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT * FROM Articole
	WHERE CodUnitate=@CodUnitate and 
	(@CodArticol='' OR CodArticol LIKE '%' + @CodArticol + '%') AND
	(@Denumire='' OR Denumire LIKE '%' + @Denumire + '%') AND 
	(@ContArticol='' OR Cont = @ContArticol) AND 
	(@ContCheltuiala='' OR ContCheltuiala =@ContCheltuiala)
	
END
GO

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Documente]') AND name = N'Dublare_Documente_Furnizor_Numar_Data')
ALTER TABLE [dbo].[Documente] DROP CONSTRAINT [Dublare_Documente_Furnizor_Numar_Data]
GO

ALTER TABLE [dbo].[Documente] ADD  CONSTRAINT [Dublare_Documente_Furnizor_Numar_Data] UNIQUE NONCLUSTERED 
(
	[IDPartener] ASC,
	[CodUnitate] ASC,
	[NrDoc] ASC,
	[DataDoc] ASC
)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_GenereazaJurnalCumparari]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_GenereazaJurnalCumparari]
GO

CREATE PROCEDURE [dbo].[fares_GenereazaJurnalCumparari] 
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint, 
	@DeLaData datetime,
	@PanaLaData datetime
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT NrDoc,DataDoc,doc.CodUnitate,part.CUI,part.Denumire,part.AtributFiscal,
docd.*
FROM 
Documente doc JOIN Parteneri part ON doc.IDPartener = part.IDPartener
JOIN TipDocument tipd ON doc.IDTipDocument = tipd.IDTipDocument
JOIN 

(SELECT dd.IDDocument,SUM(CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA) ValoareTotalaSiTVA,
  SUM(CASE WHEN dd.IDRegimFiscal IN (4,17,18,19,27) AND (dd.CotaTVA=24 OR dd.CotaTVA=19)  THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza24,
  SUM(CASE WHEN dd.IDRegimFiscal IN (4,17,18,19,27) AND (dd.CotaTVA=24 OR dd.CotaTVA=19) THEN ValoareTVA ELSE 0 END) TVA24,
  SUM(CASE WHEN dd.IDRegimFiscal IN (4,17,18,19,27) AND dd.CotaTVA=9 THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza9,
  SUM(CASE WHEN dd.IDRegimFiscal IN (4,17,18,19,27) AND dd.CotaTVA=9 THEN ValoareTVA ELSE 0 END) TVA9,
  SUM(CASE WHEN dd.IDRegimFiscal IN (6) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) InversBaza,
  SUM(CASE WHEN dd.IDRegimFiscal IN (6) THEN ValoareTVA ELSE 0 END) InversTVA,
  SUM(CASE WHEN dd.IDRegimFiscal IN (20) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA ELSE 0 END) ImportTaraScutite,
  SUM(CASE WHEN dd.IDRegimFiscal IN (27) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) ObligatBaza,
  SUM(CASE WHEN dd.IDRegimFiscal IN (27) THEN ValoareTVA ELSE 0 END) ObligatTVA,
  SUM(CASE WHEN dd.IDRegimFiscal IN (29) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) BCScutit,
  SUM(CASE WHEN dd.IDRegimFiscal IN (22) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) DRScutit,
  SUM(CASE WHEN dd.IDRegimFiscal IN (24) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) NFScutit,
  SUM(CASE WHEN dd.IDRegimFiscal IN (30) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) BCNeimp,
  SUM(CASE WHEN dd.IDRegimFiscal IN (23) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) DRNeimp,
  SUM(CASE WHEN dd.IDRegimFiscal IN (26) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) NFNeimp,
  SUM(CASE WHEN dd.IDRegimFiscal IN (28) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) BCBI,
  SUM(CASE WHEN dd.IDRegimFiscal IN (21) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) DRBI,
  SUM(CASE WHEN dd.IDRegimFiscal IN (24) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) NFBI,
  SUM(CASE WHEN dd.IDRegimFiscal IN (28) THEN ValoareTVA ELSE 0 END) BCTVA,
  SUM(CASE WHEN dd.IDRegimFiscal IN (21) THEN ValoareTVA ELSE 0 END) DRTVA,
  SUM(CASE WHEN dd.IDRegimFiscal IN (24) THEN ValoareTVA ELSE 0 END) NFTVA

   FROM DocumenteDetaliu dd JOIN Documente d ON dd.IDDocument=d.IDDocument
   GROUP BY dd.IDDocument) docd ON doc.IDDocument = docd.IDDocument
WHERE tipd.IDTipDocument in (0,10) AND doc.CodUnitate=@CodUnitate AND 
doc.DataDoc>=@DeLaData AND doc.DataDoc<=@PanaLaData
   ORDER BY DataDoc
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_GenereazaJurnalVanzari]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_GenereazaJurnalVanzari]
GO

CREATE PROCEDURE [dbo].[fares_GenereazaJurnalVanzari] 
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint, 
	@DeLaData datetime,
	@PanaLaData datetime,
	@cumpTI bit
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT NrDoc,DataDoc,doc.CodUnitate,part.CUI,part.Denumire,part.AtributFiscal,
docd.*
FROM 
Documente doc JOIN Parteneri part ON doc.IDPartener = part.IDPartener
JOIN TipDocument tipd ON doc.IDTipDocument = tipd.IDTipDocument
JOIN 
(SELECT dd.IDDocument,SUM(CASE WHEN IDTipDocument=11 THEN ValoareTotala ELSE ValoareTotala+ValoareTVA END ) ValoareTotalaSiTVA,
  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=24 THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza24,
  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=24 THEN ValoareTVA ELSE 0 END) TVA24,
  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=19 THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza19,
  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=19 THEN ValoareTVA ELSE 0 END) TVA19,
  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=9 THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza9,
  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=9 THEN ValoareTVA ELSE 0 END) TVA9,
  SUM(CASE WHEN dd.IDRegimFiscal IN (6) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) InversBaza,
  SUM(CASE WHEN dd.IDRegimFiscal IN (6) THEN ValoareTVA ELSE 0 END) InversTVA,
  SUM(CASE WHEN dd.IDRegimFiscal IN (10) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA ELSE 0 END) DeducereNoRO,
  SUM(CASE WHEN dd.IDRegimFiscal IN (13) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA ELSE 0 END) FaraDeducereNoRO,
  SUM(CASE WHEN dd.IDRegimFiscal IN (14) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) IntracomScutite1,
  SUM(CASE WHEN dd.IDRegimFiscal IN (15) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) IntracomScutite2,
  SUM(CASE WHEN dd.IDRegimFiscal IN (1) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) ScutiteCuDed,
  SUM(CASE WHEN dd.IDRegimFiscal IN (2) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) ScutiteFaraDed,
  SUM(CASE WHEN dd.IDRegimFiscal IN (16) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA ELSE 0 END) Neimpozabile,
  CASE WHEN dd.IDRegimFiscal IN (6) THEN 1 ELSE 0 END TI

   FROM DocumenteDetaliu dd join Documente a on a.IDDocument=dd.IDDocument
   GROUP BY dd.IDDocument,CASE WHEN dd.IDRegimFiscal IN (6) THEN 1 ELSE 0 END) docd ON doc.IDDocument = docd.IDDocument
   WHERE ((tipd.IDTipDocument in (1,11)) OR (@cumpTI=1 AND tipd.IDTipDocument in (0) AND TI=1))
    AND doc.CodUnitate=@CodUnitate 
    AND doc.DataDoc>=@DeLaData AND doc.DataDoc<=@PanaLaData
   ORDER BY DataDoc
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetBalanta]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetBalanta]
GO

CREATE PROCEDURE [dbo].[GetBalanta] 
	-- Add the parameters for the stored procedure here
	@pDtDataStartUI datetime,
	@pDtDataEndUI datetime,
	@pJurnal int,
	@pCategorie int,
	@pStrContStart TipCont,
	@pStrContEnd TipCont,
	@pCfm bit,
	@pAnalitica bit,
	@pCfmRulaje bit,
	@pTerminal bit,
	@An smallint,
	@Tgc bit
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
DECLARE @CodUnitate smallint
DECLARE @cmd varchar(5000)
DECLARE @strCodUnitate varchar(20)
DECLARE @DtDataStartUI varchar(20)
DECLARE @DtDataEndUI varchar(20)
DECLARE @strCategorie varchar(100)
DECLARE @strAn varchar(10)

SET @CodUnitate=0
SELECT @CodUnitate=CAST(Valoare AS smallint) FROM [SetariLocale] WHERE [CodSetare]='UCRT' AND [xUser]=suser_sname() AND [xHost]=host_name()
SET @strCodUnitate = cast(@CodUnitate AS varchar(20))

DECLARE @ConturiTGC AS varchar(2000)
SET @ConturiTGC = ','
SELECT @ConturiTGC=@ConturiTGC + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{TGC}%' AND [CodUnitate]=@CodUnitate

DECLARE @ConturiSS AS varchar(2000)
SET @ConturiSS = ','
SELECT @ConturiSS=@ConturiSS + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{CSS}%' AND [CodUnitate]=@CodUnitate



SET @DtDataStartUI = cast(YEAR(@pDtDataStartUI) as varchar(4)) + '-' +cast(MONTH(@pDtDataStartUI) as varchar(2)) + '-' + cast(DAY(@pDtDataStartUI) as varchar(2))
SET @DtDataEndUI = cast(YEAR(@pDtDataEndUI) as varchar(4)) + '-' +cast(MONTH(@pDtDataEndUI) as varchar(2)) + '-' + cast(DAY(@pDtDataEndUI) as varchar(2))

SET @strCategorie = LTRIM(RTRIM(cast(@pCategorie as varchar(10))))
SET @strAn = cast(@An as varchar(10))

IF @Tgc=1
	SET @strCategorie = @strCategorie + ',3'

SET @cmd = 'SELECT  Cont,ContParinte,Terminal,Denumire,Tip,Categorie,SortValue,DebInit [SoldInitDeb],CreInit [SoldInitCre], 
    DebPrecCalc [RulPrecDeb], CrePrecCalc [RulPrecCre], 
    DebCurent [RulCurentDeb], CreCurent [RulCurentCre],
	DebInit + DebPrecCalc+ COALESCE(DebCurent, 0) RulTotalDeb, CreInit +CrePrecCalc + COALESCE(CreCurent, 0) RulTotalCre, 
	dbo.SoldDebit(Tip, DebInit + DebPrecCalc + COALESCE(DebCurent, 0), CreInit + CrePrecCalc + COALESCE(CreCurent, 0)) SoldTotalDeb, 
	dbo.SoldCredit(Tip, DebInit + DebPrecCalc + COALESCE(DebCurent, 0), CreInit + CrePrecCalc + COALESCE(CreCurent, 0)) SoldTotalCre
	FROM
	(SELECT c.Cont, C.Denumire,C.EsteAnalitic, C.Tip,C.Categorie,C.ContParinte,C.Terminal,C.SortValue, c.DebitInitial AS [DebInit], c.CreditInitial AS [CreInit], 
	sum(COALESCE(DebPrecCalc, 0)) DebPrecCalc,sum(COALESCE(CrePrecCalc, 0)) CrePrecCalc,  
	sum(COALESCE(DebCurent, 0)) DebCurent, sum(COALESCE(CreCurent, 0)) CreCurent
	FROM '
IF @Tgc=1
BEGIN
	DECLARE @cmdCont varchar(2000)
	SET @cmdCont =  
	'(SELECT a.[Cont],a.[CodUnitate],a.[Denumire],a.[Tip],a.[Terminal],a.[ContParinte],a.[EsteAnalitic],a.[Categorie],a.[SortValue],a.[DebitPrecedent],a.[CreditPrecedent],
	sum(b.DebitInitial) [DebitInitial] ,sum(b.creditInitial) [CreditInitial]
	FROM [Conturi] a JOIN [Conturi] b
	ON dbo.IsContInclus(a.cont,b.cont,a.Terminal)=1 and a.codunitate=b.codunitate
	where a.[CodUnitate]=' + @strCodUnitate +' and a.categorie=3 and b.ContParinte=''N/A''
	group by a.[Cont],a.[CodUnitate],a.[Denumire],a.[Tip],a.[Terminal],a.[ContParinte],a.[EsteAnalitic],a.[Categorie],a.[SortValue],a.[DebitPrecedent],a.[CreditPrecedent]
	UNION
	SELECT  a2.[Cont],a2.[CodUnitate],a2.[Denumire],a2.[Tip],a2.[Terminal],a2.[ContParinte],a2.[EsteAnalitic],a2.[Categorie],a2.[DebitPrecedent],a2.[CreditPrecedent],
	a2.[DebitInitial] ,a2.[CreditInitial] FROM [Conturi] a2
	WHERE a2.codunitate='+@strCodUnitate +' and a2.categorie=0 )'
	IF @An <> 0 
		SET @cmdCont = REPLACE(@cmdCont,'[Conturi]', '[Conturi' + @strAn + ']')
	
	SET @cmd = @cmd  + @cmdCont
END
ELSE
BEGIN
	IF @An <> 0 
		SET @cmd = @cmd  + '[Conturi' + @strAn + ']'
	ELSE
		SET @cmd = @cmd  + '[Conturi]'
END

SET @cmd = @cmd  + ' c
    LEFT OUTER JOIN 
	(SELECT sum(sumaDebitPrec) DebPrecCalc, sum(sumaCreditPrec) AS CrePrecCalc,
	   sum(sumaDebit) AS DebCurent, sum(sumaCredit) AS CreCurent, Cont, CodUnitate,IDJurnal FROM
			(SELECT 			    
				coalesce(SUM(CASE WHEN D.DataDoc<''' + @DtDataStartUI + ''' THEN F.ValoareLei ELSE 0 END),0) sumaDebitPrec,
				coalesce(SUM(CASE WHEN D.DataDoc>=''' + @DtDataStartUI +''' AND D.DataDoc<=''' + @DtDataEndUI +''' THEN F.ValoareLei ELSE 0 END),0) sumaDebit, 
				0.00 AS sumaCreditPrec, 0.00 AS sumaCredit, 
				F.ContDebit AS Cont,D.CodUnitate AS CodUnitate,D.IdJurnal AS Idjurnal
				FROM '
--[RulajeDoc]
IF @An <> 0 
	SET @cmd = @cmd  + '[RulajeDoc' + @strAn + ']'
ELSE
	SET @cmd = @cmd  + '[RulajeDoc]'

SET @cmd = @cmd  + ' D INNER JOIN '
--[Rulaje]
IF @An <> 0 
	SET @cmd = @cmd  + '[Rulaje' + @strAn + ']'
ELSE
	SET @cmd = @cmd  + '[Rulaje]'


SET @cmd = @cmd  + ' F ON D.[IDDocument] = F.[IDDocument] 
				WHERE  d.[CodUnitate] =' +  @strCodUnitate
			
IF 	@pJurnal<>-1	
	SET @cmd = @cmd + 	' AND D.IDJurnal= ' + cast(@pJurnal as varchar(10)) 
					
SET @cmd = @cmd  + ' GROUP BY F.ContDebit,D.CodUnitate,D.IDJurnal
			UNION
			SELECT 
				0.00 AS sumaDebitPrec, 0.00 AS sumaDebit,
				coalesce(SUM(CASE WHEN D.DataDoc < ''' + @DtDataStartUI+''' THEN F.ValoareLei ELSE 0 END),0) sumaCreditPrec,
				coalesce(SUM(CASE WHEN D.DataDoc >=''' + @DtDataStartUI+''' AND D.DataDoc<=''' + @DtDataEndUI +''' THEN F.ValoareLei ELSE 0 END),0) sumaCredit, 
                F.ContCredit AS Cont,D.CodUnitate  AS CodUnitate,D.IDJurnal AS Idjurnal
				FROM '
--[RulajeDoc]
IF @An <> 0 
	SET @cmd = @cmd  + '[RulajeDoc' + @strAn + ']'
ELSE
	SET @cmd = @cmd  + '[RulajeDoc]'

SET @cmd = @cmd  + ' D INNER JOIN '
--[Rulaje]
IF @An <> 0 
	SET @cmd = @cmd  + '[Rulaje' + @strAn + ']'
ELSE
	SET @cmd = @cmd  + '[Rulaje]'

SET @cmd = @cmd  + ' F ON D.[IDDocument] = F.[IDDocument] 
				WHERE d.[CodUnitate] = '+@strCodUnitate 
IF 	@pJurnal<>-1	
	SET @cmd = @cmd + 	' AND D.IDJurnal= ' + cast(@pJurnal as varchar(10)) 
			

SET @cmd = @cmd +  ' GROUP BY F.ContCredit,D.CodUnitate,D.IDJurnal) t1
	GROUP BY Cont,CodUnitate,IDJurnal
	) RUL 
	ON dbo.IsContInclus(c.cont,rul.cont,c.Terminal)=1 AND c.CodUnitate=rul.CodUnitate
	WHERE  c.[Cont]<>''N/A'' AND Categorie in ('+ @strCategorie + ') AND c.[CodUnitate] ='+ @strCodUnitate + '
	AND c.Cont >= '''+@pStrContStart + ''''
IF @pStrContEnd	<> ''
	SET @cmd = @cmd + ' AND c.Cont <='''+ @pStrContEnd +''''
	
SET @cmd = @cmd +  'GROUP BY  c.Cont, C.Denumire,  C.Tip, C.Categorie, C.ContParinte,C.Terminal,C.SortValue,c.CreditInitial, CreditPrecedent,  c.DebitInitial, DebitPrecedent,C.EsteAnalitic
	) [balanta] '

DECLARE @cmdFilt varchar(8000)
SET @cmdfilt = ' WHERE ' 
IF @pCfm <> 1
BEGIN
	IF @An <> 0 
		SET @cmdfilt  = @cmdfilt +  ' (EXISTS (SELECT ContDebit, ContCredit FROM Rulaje'+@strAn+' WHERE ((ContDebit LIKE Cont + ''%'') OR (ContCredit LIKE Cont + ''%'')) AND [CodUnitate]='+@strCodUnitate+') OR ((DebInit<>0) OR (CreInit<>0)))  '
	ELSE
		SET @cmdfilt  = @cmdfilt +  ' (EXISTS (SELECT ContDebit, ContCredit FROM Rulaje WHERE ((ContDebit LIKE Cont + ''%'') OR (ContCredit LIKE Cont + ''%'')) AND [CodUnitate]='+@strCodUnitate+') OR ((DebInit<>0) OR (CreInit<>0)))  '
END

IF @pAnalitica <> 1
BEGIN
	IF @pCfm <> 1
		SET @cmdfilt  = @cmdfilt +  ' AND '
	IF @Tgc=0
		SET @cmdfilt  = @cmdfilt +  ' (([EsteAnalitic]=0)  OR (dbo.IsContIn('',''+ Cont+'','','''+@ConturiSS+''')<>0)) '
	ELSE
		SET @cmdfilt  = @cmdfilt +  ' (([EsteAnalitic]=0) OR (dbo.IsContIn('',''+ Cont+'','','''+@ConturiTGC+''')<>0)) '

END
IF @pCfmRulaje <> 1
BEGIN
	IF (@pCfm <> 1) OR (@pAnalitica <> 1)
		SET @cmdfilt  = @cmdfilt +  ' AND '
	SET @cmdfilt  = @cmdfilt +  ' (DebCurent<>0 OR CreCurent<>0) '
END

IF @pTerminal <> 0
BEGIN
	IF (@pCfm <> 1) OR (@pAnalitica <> 1) OR (@pCfmRulaje <> 1)
		SET @cmdfilt  = @cmdfilt +  ' AND '
	SET @cmdfilt  = @cmdfilt +  '([Terminal]=1)'
END
IF @cmdfilt <> ' WHERE '
	SET @cmd  = @cmd +  @cmdFilt
SET @cmd  = @cmd +  ' ORDER BY Cont '
PRINT(@cmdFilt)
PRINT(@cmd)
EXEC(@cmd) 
IF @@ERROR != 0 GOTO EROARE  GOTO Gata
EROARE:
	ROLLBACK
	RAISERROR ('Eroare balanta!',16,-1)  GOTO Gata
EROARE1:
	RAISERROR ('!!',16,-1)	GOTO Gata
Gata:

END

GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'RegimFiscal' AND COLUMN_NAME = 'CuTVA')
BEGIN
ALTER TABLE dbo.RegimFiscal ADD
	CuTVA bit NOT NULL CONSTRAINT DF_RegimFiscal_CuTVA DEFAULT 1
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'RegimFiscal' AND COLUMN_NAME = 'TipOperatie')
BEGIN
ALTER TABLE dbo.RegimFiscal ADD
	TipOperatie varchar(10) NOT NULL DEFAULT ''
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'RegimFiscal' AND COLUMN_NAME = 'Vizibil')
BEGIN
ALTER TABLE dbo.RegimFiscal ADD
	Vizibil bit NOT NULL DEFAULT 1
END
GO


UPDATE RegimFiscal SET CuTVA=0 WHERE IDRegimFiscal IN (1,2,7,10,13,14,15,16,22,23,25,26,29,30)
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'FaresSetup' AND COLUMN_NAME = 'SalveazaCuUrmatorulNumar')
BEGIN
ALTER TABLE dbo.FaresSetup ADD
	SalveazaCuUrmatorulNumar bit NOT NULL CONSTRAINT DF_FaresSetup_SalveazaCuUrmatorulNumar DEFAULT 1
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'FaresSetup' AND COLUMN_NAME = 'IDSeriePlati')
BEGIN
ALTER TABLE dbo.FaresSetup ADD
	IDSeriePlati varchar(20) NOT NULL DEFAULT ''
END
GO

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[audit_Document]'))
DROP TRIGGER [dbo].[audit_Document]
GO

CREATE TRIGGER [dbo].[audit_Document] 
   ON  [dbo].[Documente]
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @modifDoc bit
	SELECT @modifDoc = SalveazaCuUrmatorulNumar FROM FaresSetup a JOIN inserted i ON
		a.[CodUnitate]=i.CodUnitate
	IF @modifDoc=1
		UPDATE [Documente] SET NrDoc = (SELECT MAX(CAST([NrDoc] AS decimal(18,0)))+1 FROM [Documente] a WHERE 
			a.CodUnitate=[Documente].[CodUnitate]) FROM inserted i
		WHERE i.IDDocument=[Documente].[IDDocument]  AND NOT EXISTS(SELECT * FROM deleted d WHERE d.IDDocument=i.IDDocument)

    INSERT INTO [admin_Audit]
           ([UserId]
           ,[TableName]
           ,[Operation]
           ,[CampID]
           ,[Data])
     SELECT [UserID],'Documente', 'A', IDDocument, getdate() FROM inserted i
		WHERE i.IDDocument not in (SELECT IDDocument from deleted)	 

    INSERT INTO [admin_Audit]
           ([UserId]
           ,[TableName]
           ,[Operation]
           ,[CampID]
           ,[Data])
     SELECT i.[UserID],'Documente', 'M', i.IDDocument, getdate() FROM inserted i
		JOIN deleted d on i.IDDocument=d.IDDocument

    INSERT INTO [admin_Audit]
           ([UserId]
           ,[TableName]
           ,[Operation]
           ,[CampID]
           ,[Data])
     SELECT [UserID],'Documente', 'D', IDDocument, getdate() FROM deleted d
		WHERE d.IDDocument not in (SELECT IDDocument from inserted)	 

	

END

GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Parteneri_Conturi]') AND parent_object_id = OBJECT_ID(N'[dbo].[Parteneri]'))
ALTER TABLE dbo.Parteneri DROP CONSTRAINT FK_Parteneri_Conturi
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TipDocument' AND COLUMN_NAME = 'SePlateste')
BEGIN
	ALTER TABLE TipDocument ADD SePlateste bit NOT NULL DEFAULT 1
END
GO

DECLARE @cate int

SELECT @Cate = COUNT(*) FROM [TipDocument] WHERE [IDTipDocument] IN (10)
IF @Cate<>1
BEGIN
	SET IDENTITY_INSERT [TipDocument] ON
	INSERT INTO [TipDocument]([IDTipDocument],[Denumire],[TipOperatie],[SePlateste]) VALUES (10,'Bon Fiscal','I',1)
	INSERT INTO [TipDocument]([IDTipDocument],[Denumire],[TipOperatie],[SePlateste]) VALUES (11,'Bon Fiscal','O',1)
	SET IDENTITY_INSERT [TipDocument] OFF

	INSERT INTO [admin_ApplicationDocuments]([IDTipDocument],[ApplicationID]) VALUES (10,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9')
	INSERT INTO [admin_ApplicationDocuments]([IDTipDocument],[ApplicationID]) VALUES (11,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9')


END
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[admin_RoleInMenu]') AND type in (N'U'))
	DELETE FROM [admin_RoleInMenu]
GO

SET IDENTITY_INSERT [admin_ApplicationMenu] ON
DELETE FROM [admin_ApplicationMenu] WHERE [ApplicationId] = '892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9'

INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3100,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Documente intrate(cumparare)',NULL,100,'Documente_intrare',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3200,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Documente iesite(vanzare)',NULL,110,'Documente_iesire',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3500,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Incasari, plati',NULL,115,'Incasari_Plati',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3300,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Parteneri',NULL,120,'Parteneri',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(4200,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Articole',NULL,130,'Articole',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3400,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Setari',NULL,140,NULL,NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(4100,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Import date',NULL,150,'Import',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(4200,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Consultare Arhiva',NULL,155,'Consultare_Arhiva',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(4800,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Liste',NULL,160,NULL,NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(4000,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Unitati functionale',NULL,170,'Unitati_functionale',NULL,NULL)

INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3401,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Parametrizare Modul',3400,20,'ParametrizareModul',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3402,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Banci',3400,21,'Banci',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3403,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Trasee',3400,22,'Trasee',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3404,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Locatii vizitate',3400,23,'Locatii',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3405,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Regim fiscal',3400,24,'Regimuri_Fiscale',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3406,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Coduri CPV',3400,25,'CoduriCPV',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3407,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Categorii Articole',3400,26,'CategoriiArticole',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3408,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Tipuri Dimensiuni',3400,27,'TipuriDimensiuni',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3409,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Note Contabile',3400,28,'NoteContabile',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3410,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Semnaturi',3400,29,'Semnaturi',NULL,NULL)

INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(4801,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Categorii Articole',4800,40,'RaportCategoriiArticole',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(4802,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Jurnale',4800,41,'RaportJurnale',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(4803,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Liste Documente',4800,42,'RaportDocumente',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(4804,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Operatiuni Contabile',4800,43,'RaportContabil',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(4805,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Facturi',4800,44,'RaportFacturi',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(4806,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Operatiuni incasare/plata',4800,45,'RaportOperatiuniIncasarePlata',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(4807,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Fisa Partenerului',4800,46,'RaportFisaPartenerului',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(4808,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Export Declaratii',4800,47,'Semnaturi',NULL,NULL)
SET IDENTITY_INSERT [admin_ApplicationMenu] OFF	
GO

SET IDENTITY_INSERT [admin_ApplicationMenu] ON
DELETE FROM [admin_ApplicationMenu] WHERE [ApplicationId] = 'F9B96690-0736-4F91-BC2C-F434651A1AFF'
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2001,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Fisa Mijlocului Fix',NULL,1,'FisaMFix',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2002,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Obiecte de Inventar',NULL,2,'ObiecteInventar',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2003,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Documente',NULL,3,'Documente',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2004,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Inventar - Stoc Faptic',NULL,4,'StocFaptic',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2005,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Rapoarte',NULL,5,NULL,NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2006,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Setari',NULL,6,NULL,NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2007,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Unitati functionale',NULL,7,'Unitati_functionale',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2008,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Despre modul',NULL,8,NULL,NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2109,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Calculul amortizarii',2005,9,'Calculul_Amortizarii',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2100,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Registrul numerelor de inventar',2005,10,'Repartizare_Amortizare',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2101,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Lista de inventar',2005,11,'Lista_Inventar',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2102,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Fisa mijlocului fix',2005,12,'Fisa_Mijlocului_Fix',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2103,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Lista documentelor',2005,13,'Lista_Documentelor',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2104,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Obiecte inventar',2005,14,'Obiecte_Inventar',NULL,NULL)

INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2204,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Parametrizare modul',2006,14,'Parametrizare',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2205,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Departamente',2006,15,'Departamente',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2206,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Catalog mijloace fixe',2006,16,'Catalog',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2207,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Gestionari',2006,17,'Gestionari',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2208,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Import',2006,18,'Import',NULL,NULL)
SET IDENTITY_INSERT [admin_ApplicationMenu] OFF	
GO


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Documente' AND COLUMN_NAME = 'NrDocReceptie')
BEGIN
	ALTER TABLE dbo.Documente ADD NrDocReceptie varchar(20) NULL
	ALTER TABLE dbo.Documente ADD DataDocReceptie datetime NULL
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TipDimensiune' AND COLUMN_NAME = 'Vizibila')
BEGIN
ALTER TABLE dbo.TipDimensiune ADD
	Vizibila bit NOT NULL CONSTRAINT DF_TipDimensiune_Vizibila DEFAULT 1
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'DocumenteDetaliu' AND COLUMN_NAME = 'ValoareTotalaCuAdaos')
BEGIN
ALTER TABLE dbo.DocumenteDetaliu ADD
	ValoareTotalaCuAdaos decimal(25,5) NOT NULL DEFAULT 0
END
GO


DECLARE @cate int
SELECT @Cate = COUNT(*) FROM [TipDimensiune]
IF @Cate=1
BEGIN
	SET IDENTITY_INSERT [TipDimensiune] ON
	INSERT INTO [TipDimensiune] ([IDTipDimensiune],[Denumire],[Format],[Vizibila])VALUES(1,'Dimensiune1','SL',0)
	INSERT INTO [TipDimensiune] ([IDTipDimensiune],[Denumire],[Format],[Vizibila])VALUES(2,'Dimensiune2','SL',0)
	INSERT INTO [TipDimensiune] ([IDTipDimensiune],[Denumire],[Format],[Vizibila])VALUES(3,'Dimensiune3','SL',0)
	INSERT INTO [TipDimensiune] ([IDTipDimensiune],[Denumire],[Format],[Vizibila])VALUES(4,'Dimensiune4','SL',0)
	INSERT INTO [TipDimensiune] ([IDTipDimensiune],[Denumire],[Format],[Vizibila])VALUES(5,'Dimensiune5','SL',0)
	INSERT INTO [TipDimensiune] ([IDTipDimensiune],[Denumire],[Format],[Vizibila])VALUES(6,'Dimensiune6','SL',0)
	INSERT INTO [TipDimensiune] ([IDTipDimensiune],[Denumire],[Format],[Vizibila])VALUES(7,'Dimensiune7','SL',0)
	SET IDENTITY_INSERT [TipDimensiune] OFF
END
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[fares_DocumenteDimensiuniLinie]'))
DROP VIEW [dbo].[fares_DocumenteDimensiuniLinie]
GO

CREATE VIEW [dbo].[fares_DocumenteDimensiuniLinie]
AS
SELECT     a.Tabela, a.IDCheieTabela, a.CodUnitate, v1.Valoare AS valoare1, v2.Valoare AS valoare2, v3.Valoare AS valoare3, v4.Valoare AS valoare4
FROM         (SELECT DISTINCT Tabela, IDCheieTabela, CodUnitate
                       FROM          dbo.ValoriDimensiuni) AS a LEFT OUTER JOIN
                      dbo.ValoriDimensiuni AS v1 ON v1.IDTipDimensiune = 0 AND v1.CodUnitate = a.CodUnitate AND a.Tabela = v1.Tabela AND 
                      a.IDCheieTabela = v1.IDCheieTabela LEFT OUTER JOIN
                      dbo.ValoriDimensiuni AS v2 ON v2.IDTipDimensiune = 1 AND v2.CodUnitate = a.CodUnitate AND a.Tabela = v2.Tabela AND 
                      a.IDCheieTabela = v2.IDCheieTabela LEFT OUTER JOIN
                      dbo.ValoriDimensiuni AS v3 ON v3.IDTipDimensiune = 2 AND v3.CodUnitate = a.CodUnitate AND a.Tabela = v3.Tabela AND 
                      a.IDCheieTabela = v3.IDCheieTabela LEFT OUTER JOIN
                      dbo.ValoriDimensiuni AS v4 ON v4.IDTipDimensiune = 3 AND v4.CodUnitate = a.CodUnitate AND a.Tabela = v4.Tabela AND 
                      a.IDCheieTabela = v4.IDCheieTabela
WHERE     (a.Tabela = 'Documente')
GO


IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[fares_DocumenteDimensiuni]'))
DROP VIEW [dbo].[fares_DocumenteDimensiuni]
GO


CREATE VIEW [dbo].[fares_DocumenteDimensiuni]
AS
SELECT     a.IDDocument, a.NrDoc, a.DataDoc, a.CodUnitate, a.IDTipDocument, a.NrContract, a.DataContract, a.Observatii, a.DataScadenta, a.NrDocReceptie, a.DataDocReceptie, 
                      dbo.TipDocument.TipOperatie, dbo.TipDocument.Denumire, c.valoare1, c.valoare2, c.valoare3, c.valoare4, p.Denumire AS DenPartener, dd.ValoareTVA, 
                      CASE WHEN a.IDTipDocument IN (10, 11) THEN dd.ValoareTotala - dd.ValoareTVA ELSE dd.ValoareTotala END AS ValoareTotala, dd.CotaTVA, 
                      dd.ValoareTotalaCuAdaos, CASE WHEN a.IDTipDocument IN (10, 11) THEN (dd.ValoareTotalaCuAdaos * 100) / (dd.CotaTVA + 100) - (dd.ValoareTotala * 100) 
                      / (dd.CotaTVA + 100) ELSE (dd.ValoareTotalaCuAdaos * 100) / (dd.CotaTVA + 100) - dd.ValoareTotala END AS Adaos, (dd.ValoareTotalaCuAdaos * dd.CotaTVA) 
                      / (dd.CotaTVA + 100) AS TVANeexigibil, ar.Denumire AS DenArticol, p.IDPartener, ar.IDArticol
FROM         dbo.Documente AS a INNER JOIN
                      dbo.Parteneri AS p ON a.IDPartener = p.IDPartener INNER JOIN
                      dbo.TipDocument ON a.IDTipDocument = dbo.TipDocument.IDTipDocument LEFT OUTER JOIN
                      dbo.fares_DocumenteDimensiuniLinie AS c ON a.IDDocument = c.IDCheieTabela AND a.CodUnitate = c.CodUnitate INNER JOIN
                      dbo.DocumenteDetaliu AS dd ON a.IDDocument = dd.IDDocument INNER JOIN
                      dbo.Articole AS ar ON dd.IDArticol = ar.IDArticol

GO


IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GenNoteContabile]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[GenNoteContabile](
		[IDNotaContabila] [int] IDENTITY(1,1) NOT NULL,
		[IDTipDocument] [smallint] NOT NULL,
		[ContDebit] [dbo].[TipCont] NOT NULL,
		[ContCredit] [dbo].[TipCont] NOT NULL,
		[Valoare] [varchar](50) NOT NULL,
	 CONSTRAINT [PK_GenNoteContabile] PRIMARY KEY CLUSTERED 
	(
		[IDNotaContabila] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[GenNoteContabile]  WITH CHECK ADD  CONSTRAINT [FK_GenNoteContabile_TipDocument] FOREIGN KEY([IDTipDocument])
	REFERENCES [dbo].[TipDocument] ([IDTipDocument])

	ALTER TABLE [dbo].[GenNoteContabile] CHECK CONSTRAINT [FK_GenNoteContabile_TipDocument]

END
GO

DECLARE @cate int
SELECT @Cate = COUNT(*) FROM [TipDocument] WHERE [IDTipDocument]<10
IF @Cate= 0 
BEGIN
	SET IDENTITY_INSERT [TipDocument] ON
	INSERT INTO [tipDocument] ([IDTipDocument],[Denumire],[TipOperatie],[SePlateste])VALUES(0,'Factura','I',1)
	INSERT INTO [tipDocument] ([IDTipDocument],[Denumire],[TipOperatie],[SePlateste])VALUES(1,'Factura','O',1)
	INSERT INTO [tipDocument] ([IDTipDocument],[Denumire],[TipOperatie],[SePlateste])VALUES(2,'Intrare','I',0)
	INSERT INTO [tipDocument] ([IDTipDocument],[Denumire],[TipOperatie],[SePlateste])VALUES(3,'Iesire','O',0)
	INSERT INTO [tipDocument] ([IDTipDocument],[Denumire],[TipOperatie],[SePlateste])VALUES(4,'Transfer Intern','IO',0)
	INSERT INTO [tipDocument] ([IDTipDocument],[Denumire],[TipOperatie],[SePlateste])VALUES(5,'Conservare','IO',0)
	INSERT INTO [tipDocument] ([IDTipDocument],[Denumire],[TipOperatie],[SePlateste])VALUES(6,'Reactivare','I',0)
	INSERT INTO [tipDocument] ([IDTipDocument],[Denumire],[TipOperatie],[SePlateste])VALUES(7,'Modificare Mijloc Fix','I',0)
	INSERT INTO [tipDocument] ([IDTipDocument],[Denumire],[TipOperatie],[SePlateste])VALUES(8,'Modernizare','I',0)
	INSERT INTO [tipDocument] ([IDTipDocument],[Denumire],[TipOperatie],[SePlateste])VALUES(9,'Comanda','O',0)
	SET IDENTITY_INSERT [TipDocument] OFF
END
GO

DECLARE @cate int
SELECT @Cate = COUNT(*) FROM [GenNoteContabile]
IF @Cate= 0 
BEGIN
	SET IDENTITY_INSERT [GenNoteContabile] ON
	INSERT INTO [GenNoteContabile] ([IDNotaContabila],[IDTipDocument],[ContDebit],[ContCredit],[Valoare])VALUES(1,0,'{ART.CNT}','{PRT.CNT}','{DOC.VAL}')
	INSERT INTO [GenNoteContabile] ([IDNotaContabila],[IDTipDocument],[ContDebit],[ContCredit],[Valoare])VALUES(2,0,'{TVA.DED}','{PRT.CNT}','{DOC.TVA}')
	INSERT INTO [GenNoteContabile] ([IDNotaContabila],[IDTipDocument],[ContDebit],[ContCredit],[Valoare])VALUES(3,1,'{PRT.CNT}','{ART.CNT}','{DOC.VAL}')
	INSERT INTO [GenNoteContabile] ([IDNotaContabila],[IDTipDocument],[ContDebit],[ContCredit],[Valoare])VALUES(4,1,'{PRT.CNT}','{TVA.COL}','{DOC.TVA}')
	SET IDENTITY_INSERT [GenNoteContabile] OFF
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_FiltreazaRaportOpConturi]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_FiltreazaRaportOpConturi]
GO

CREATE PROCEDURE [dbo].[fares_FiltreazaRaportOpConturi] 
	-- Add the parameters for the stored procedure here
	@data1 varchar(20),
	@data2 varchar(20),
	@filtru varchar(500)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @sel varchar(2000)
	SET @sel = 'SELECT * FROM [fares_DocumenteDimConturi] WHERE ' + @filtru +
	 ' AND [DataDoc]>=''' + @data1 + ''' AND [DataDoc]<=''' + @data2 +''' '
	EXEC(@sel)
END
GO




IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_FiltreazaRaportDocumente]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_FiltreazaRaportDocumente]
GO

CREATE PROCEDURE [dbo].[fares_FiltreazaRaportDocumente] 
	-- Add the parameters for the stored procedure here
	@data1 varchar(20),
	@data2 varchar(20),
	@filtru varchar(500)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @sel varchar(2000)
	SET @sel = 'SELECT * FROM [fares_DocumenteDimensiuni] WHERE ' + @filtru +
	 ' AND [DataDoc]>=''' + @data1 + ''' AND [DataDoc]<=''' + @data2 +''' ORDER BY [DataDoc]'
	EXEC(@sel)
END
GO

DROP INDEX IX_Parteneri ON dbo.Parteneri
GO

CREATE UNIQUE NONCLUSTERED INDEX IX_Parteneri ON dbo.Parteneri
	(
	CUI,
	CodUnitate
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_ParteneriProprii]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_ParteneriProprii]
GO

CREATE PROCEDURE [dbo].[fares_ParteneriProprii]
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Se insereaza toti partenerii din alte unitati in unitatea proprie
    INSERT INTO [Parteneri]
           ([CUI],[Denumire],[IDBanca],[ContBancar],[Cont],[CodUnitate],[TipPartener],[IDJudet],[IDLocalitate]
           ,[Strada],[Nr],[AtributFiscal],[JudetRegComert],[NrRegComert],[AnRegComert],[NumeDelegat],[SerieAI],[NrAI],[MijlocTransport]
           ,[NrMijlocTransport],[CodPostal],[Telefon],[Fax])
    SELECT [CUI],[Denumire],[IDBanca],[ContBancar],[Cont],@CodUnitate,[TipPartener],[IDJudet],[IDLocalitate]
           ,[Strada],[Nr],[AtributFiscal],[JudetRegComert],[NrRegComert],[AnRegComert],[NumeDelegat],[SerieAI],[NrAI],[MijlocTransport]
           ,[NrMijlocTransport],[CodPostal],[Telefon],[Fax] FROM [Parteneri] WHERE CodUnitate<>@CodUnitate
           AND (IDPartener IN (SELECT IDPartener FROM Documente WHERE CodUnitate=@CodUnitate) OR 
				IDPartener IN (SELECT IDPartener FROM DocumentePlata WHERE CodUnitate=@CodUnitate) OR
				IDPartener IN (SELECT IDFurnizor FROM Ordonantari WHERE CodUnitate=@CodUnitate))

	--modifica partenerii in noii parteneri
	UPDATE [Documente]
		SET IDPartener = b.IDPartener  FROM Parteneri a, Parteneri b
	WHERE  a.IDPartener = [Documente].IDPartener AND a.CUI = b.CUI AND b.CodUnitate = @CodUnitate AND
	[Documente].[CodUnitate]=@CodUnitate AND [Documente].IDPartener NOT IN (SELECT IDPartener FROM Parteneri WHERE @CodUnitate = [CodUnitate])
	
	-- documente plata
	UPDATE [DocumentePlata]
		SET IDPartener = b.IDPartener  FROM Parteneri a, Parteneri b
	WHERE  a.IDPartener = [DocumentePlata].IDPartener AND a.CUI = b.CUI AND b.CodUnitate = @CodUnitate AND
	[DocumentePlata].[CodUnitate]=@CodUnitate AND 
	[DocumentePlata].IDPartener NOT IN (SELECT IDPartener FROM Parteneri WHERE @CodUnitate = [CodUnitate])

	--modifica partenerii in noii parteneri la ordonantari
	UPDATE [Ordonantari]
		SET IDFurnizor = b.IDPartener  FROM Parteneri a, Parteneri b
	WHERE  a.IDPartener = [Ordonantari].IDFurnizor and a.CUI = b.CUI AND b.CodUnitate = @CodUnitate AND
	[Ordonantari].[CodUnitate]=@CodUnitate AND [Ordonantari].IDFurnizor NOT IN (SELECT IDPartener FROM Parteneri WHERE @CodUnitate = [CodUnitate])

	
	---sterge partenerii vechi ca sa nu se dubleze
	DELETE Parteneri FROM Parteneri a WHERE 
	CUI=a.CUI AND CodUnitate<>@CodUnitate AND a.CodUnitate = @CodUnitate AND
	(IDPartener NOT IN (SELECT IDPartener FROM Documente WHERE CodUnitate<>@CodUnitate) AND 
     IDPartener NOT IN (SELECT IDPartener FROM DocumentePlata WHERE CodUnitate<>@CodUnitate) AND
     IDPartener NOT IN (SELECT IDFurnizor FROM Ordonantari WHERE CodUnitate<>@CodUnitate))
 

END
GO




IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[fares_DocumentePlatite]'))
DROP VIEW [dbo].[fares_DocumentePlatite]
GO

CREATE VIEW [dbo].[fares_DocumentePlatite]
AS
SELECT     dbo.DocumentePlataDetaliu.IDDocPlataDetaliu, dbo.DocumentePlataDetaliu.IDDocumentPlata, dbo.DocumentePlataDetaliu.Valoare, 
                      dbo.Documente.NrDoc + '/' + CONVERT(varchar(20), dbo.Documente.DataDoc, 101) AS NrDataDoc, dbo.DocumentePlataDetaliu.IDDocument
FROM         dbo.DocumentePlataDetaliu INNER JOIN
                      dbo.Documente ON dbo.DocumentePlataDetaliu.IDDocument = dbo.Documente.IDDocument

GO


SET IDENTITY_INSERT [admin_ApplicationMenu] ON
DELETE FROM [admin_ApplicationMenu] WHERE [ApplicationId] = '98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959'
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9002,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Conturi',NULL,1,'Evidenta_Conturi',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9003,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Note Contabile',NULL,2,'Evidenta_NotaContabila',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9004,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Rapoarte',NULL,3,NULL,NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9005,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Situatii Financiare',NULL,4,NULL,NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9006,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Setari',NULL,5,NULL,NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9007,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Consultare Arhiva',NULL,6,'Consultare_Arhiva',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9008,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Unitati Functionale',NULL,7,'Unitati_functionale',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9009,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Generare Note Contabile',NULL,8,'GenerareNoteContabile',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9010,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Despre Modul',NULL,9,'Despre_Modul',NULL,NULL)

INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9500,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Balanta',9004,89,'Rapoarte','1',NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9511,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Fisa Contului',9004,90,'Rapoarte','2',NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9512,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Cartea Mare',9004,91,'Rapoarte','3',NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9513,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Registrul Jurnal',9004,92,'Rapoarte','4',NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9514,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Registrul de Casa',9004,93,'Rapoarte','5',NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9515,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Extras de Cont',9004,94,'Rapoarte','6',NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9516,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Fise Generale',9004,95,'Rapoarte','7',NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9517,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Bilant',9004,96,'Rapoarte','8',NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9518,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Operatiuni Diverse',9004,97,'Rapoarte','10',NULL)

INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9619,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Contul de executie (institutie)',9005,98,'ContExecutie',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9620,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Executie Cheltuieli - simplificata',9005,99,'ExecutieCheltuieli',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9621,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Lista Operatiuni Bugetare',9005,120,'OperatiuniBugetare',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9622,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Cont de Rezultat Patrimonial',9005,121,'ContRezultat',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9623,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Cont de Profit si Pierdere',9005,122,'ContProfit',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9624,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Situatia Veniturilor si cheltuielilor',9005,123,'VenituriSiCheltuieli',NULL,NULL)

INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9725,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Parametrizare Modul',9006,124,'Parametrizare',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9726,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Serii Numerice',9006,125,'SeriiNumerice',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9727,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Conturi Corespondente (Inchidere)',9006,126,'ConturiCorespondente',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9728,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Capitole/Jurnale',9006,127,'Jurnale',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9729,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Parametrizare Liste',9006,128,'Parametrizare_Liste',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9730,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Import',9006,129,'Parametrizare_Import',NULL,NULL)
SET IDENTITY_INSERT [admin_ApplicationMenu] OFF
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InitConturiCoresp]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[InitConturiCoresp]
GO

CREATE PROCEDURE [dbo].[InitConturiCoresp]
AS
	SET NOCOUNT ON 
	DECLARE @CodUnitate smallint
	SET @CodUnitate=0
	SELECT @CodUnitate=CAST(Valoare AS smallint) FROM [SetariLocale] WHERE [CodSetare]='UCRT' AND [xUser]=suser_sname() AND [xHost]=host_name()
	
	--PRINT(@CodUnitate) 
	DECLARE @ContEc TipCont
	SELECT @ContEc = Cont FROM ConturiSpeciale WHERE (Tipuri = '{REX}') AND [CodUnitate]=@CodUnitate
	--PRINT(@ContEc)
	--daca nu este definit contul rezultat se adauga unul implicit
	IF @ContEc is null
	BEGIN
		--GOTO ErrorContCoresp
		INSERT INTO [ConturiSpeciale]([Cont],[CodUnitate],[Tipuri],[Descriere])
		SELECT MIN([Cont]),MIN([CodUnitate]),'{REX}','Rezultat patrimonial' FROM Conturi WHERE Denumire like '%rezult%' AND CodUnitate = @CodUnitate

	END	
	DECLARE @ContRez TipCont 

	SELECT TOP 1 @ContRez = Cont FROM [Conturi] WHERE Cont LIKE @ContEc + '%' AND Terminal=1 AND [CodUnitate] = @CodUnitate

	INSERT INTO [ConturiCoresp] ([ContCredit],[ContDebit],[IdJurnal],[Tip],[CodUnitate])
	SELECT Cont AS [ContCredit], 
	(@ContRez) AS [Contdebit],
	[IDJurnal],
	'IC' AS [Tip],
	@CodUnitate
	FROM  [Conturi] a JOIN  (SELECT DISTINCT [IDJurnal] FROM [RulajeDoc] WHERE [CodUnitate]=@CodUnitate) b ON dbo.GetContClass(a.Cont) = '6' AND a.Terminal = 1   AND
	NOT EXISTS( SELECT [IDjurnal],[ContCredit] FROM [ConturiCoresp] x where  [ContCredit]=a.cont 
	and [IDJurnal] = b.[IdJurnal] AND x.[CodUnitate]=@CodUnitate) AND [CodUnitate]=@CodUnitate    
	
	INSERT INTO [ConturiCoresp] ([ContDebit],[ContCredit],[IdJurnal],[Tip],[CodUnitate])
	SELECT Cont AS [ContDebit], 
	(@ContRez) AS [ContCredit],
	[IDJurnal],
	'IC' AS [Tip],
	@CodUnitate
	FROM  [Conturi] a JOIN  (SELECT DISTINCT [IDJurnal] FROM [RulajeDoc] WHERE [CodUnitate]=@CodUnitate) b ON dbo.GetContClass(a.Cont) = '7' AND a.Terminal = 1  AND
	NOT EXISTS(SELECT [IDjurnal],[ContDebit] FROM [ConturiCoresp] x where  [ContDebit]=a.cont 
	and [IDJurnal] = b.[IdJurnal] AND x.[CodUnitate]=@CodUnitate) AND [CodUnitate]=@CodUnitate
	
	DELETE FROM [ConturiCoresp] WHERE 
	NOT EXISTS(
	SELECT [Cont] FROM [Conturi] WHERE Terminal = 1 and [Cont]=[ConturiCoresp].ContDebit and [CodUnitate]=@CodUnitate)
	OR
	NOT EXISTS(
	SELECT [Cont] FROM [Conturi] WHERE Terminal = 1 and [Cont]=[ConturiCoresp].ContCredit  and [CodUnitate]=@CodUnitate)
	
	GOTO Gata
	Error:
	ErrorContCoresp:
		RAISERROR ('Nu a fost definit nici un cont rezultat!',16,-1)
	Gata:
	RETURN
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'FaresSetup' AND COLUMN_NAME = 'SalvareCuAdaugare')
BEGIN
	ALTER TABLE dbo.FaresSetup ADD
	SalvareCuAdaugare bit NOT NULL CONSTRAINT DF_FaresSetup_SalvareCuAdaugare DEFAULT 0	
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'FaresSetup' AND COLUMN_NAME = 'UtilizeazaDepartament')
BEGIN
	ALTER TABLE dbo.FaresSetup ADD
	UtilizeazaDepartament bit NOT NULL DEFAULT 0	
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'FaresSetup' AND COLUMN_NAME = 'CotaTVADetaliuDocument')
BEGIN
	ALTER TABLE dbo.FaresSetup ADD
	CotaTVADetaliuDocument bit NOT NULL DEFAULT 1	
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'FaresSetup' AND COLUMN_NAME = 'RegimFiscalDetaliu')
BEGIN
	ALTER TABLE dbo.FaresSetup ADD
	RegimFiscalDetaliu bit NOT NULL DEFAULT 0	
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'FaresSetup' AND COLUMN_NAME = 'FolosesteValoareCuAdaos')
BEGIN
	ALTER TABLE dbo.FaresSetup ADD
	FolosesteValoareCuAdaos bit NOT NULL DEFAULT 0	
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[erpforms_FiltreazaParteneri]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[erpforms_FiltreazaParteneri]
GO

CREATE PROCEDURE [dbo].[erpforms_FiltreazaParteneri] 
	-- Add the parameters for the stored procedure here
	@filtru varchar(5000)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @sel varchar(8000)
	SET @sel = 'SELECT * FROM [Parteneri] WHERE ' +@filtru 
	EXEC(@sel)
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Parteneri' AND COLUMN_NAME = 'ContClient')
BEGIN
	ALTER TABLE dbo.Parteneri ADD ContClient dbo.TipCont NULL
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_FiltreazaDocumente]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_FiltreazaDocumente]
GO

CREATE PROCEDURE [dbo].[fares_FiltreazaDocumente] 
	-- Add the parameters for the stored procedure here
	@filtru varchar(500)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @sel varchar(2000)
	SET @sel = 'SELECT * FROM [Documente] where IDDocument in (select [Documente].IDDocument FROM [Documente]  JOIN [DocumenteDetaliu]  ON [Documente].IDDocument = [DocumenteDetaliu].IDDocument WHERE ' +@filtru  + ')'
	EXEC(@sel)
END

GO


IF  NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[fares_DocumenteImportate]') AND type = N'U')
BEGIN
	CREATE TABLE [dbo].[fares_DocumenteImportate](
		[Cheie] [bigint] NOT NULL,
		[Tabela] [varchar](20) NOT NULL,
		[Operatie] [varchar](10) NOT NULL,
		[Data] [datetime] NOT NULL,
		[CodUnitate] [smallint] NOT NULL,
	 CONSTRAINT [PK_fares_DocumenteImportate] PRIMARY KEY CLUSTERED 
	(
		[Cheie] ASC,
		[Tabela] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]


	ALTER TABLE [dbo].[fares_DocumenteImportate]  WITH CHECK ADD  CONSTRAINT [FK_fares_DocumenteImportate_Unitati] FOREIGN KEY([CodUnitate])
	REFERENCES [dbo].[Unitati] ([CodUnitate])

	ALTER TABLE [dbo].[fares_DocumenteImportate] CHECK CONSTRAINT [FK_fares_DocumenteImportate_Unitati]

	ALTER TABLE [dbo].[fares_DocumenteImportate] ADD  CONSTRAINT [DF_fares_DocumenteImportate_Data]  DEFAULT (getdate()) FOR [Data]

	ALTER TABLE [dbo].[fares_DocumenteImportate] ADD  CONSTRAINT [DF_fares_DocumenteImportate_CodUnitate]  DEFAULT ((0)) FOR [CodUnitate]

END
GO


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Articole' AND COLUMN_NAME = 'EsteServiciu')
BEGIN
	ALTER TABLE dbo.Articole ADD
	EsteServiciu bit NOT NULL CONSTRAINT DF_Articole_EsteServiciu DEFAULT 0
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Documente' AND COLUMN_NAME = 'DataScadenta')
BEGIN
	ALTER TABLE dbo.Documente ADD DataScadenta datetime NULL
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'DocumentePlata' AND COLUMN_NAME = 'DataExtras')
BEGIN
ALTER TABLE dbo.DocumentePlata ADD DataExtras datetime NULL
END
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[fares_Facturi]'))
DROP VIEW [dbo].[fares_Facturi]
GO

CREATE VIEW [dbo].[fares_Facturi]
AS
SELECT     a.IDPartener, a.NrDoc, a.DataDoc, ROUND(a.Total, 2) AS Total, COALESCE (b.Valoare, 0) AS Valoare, ROUND(a.Total, 2) - COALESCE (b.Valoare, 0) AS RestPlata, 
                      a.DataScadenta, dbo.Parteneri.Denumire, a.IDTipDocument, a.CodUnitate, CAST(a.IDDocument AS varchar(50)) AS IDDocument
FROM         dbo.Documente AS a LEFT OUTER JOIN
                          (SELECT     IDDocument, SUM(Valoare) AS Valoare
                            FROM          dbo.DocumentePlataDetaliu
                            GROUP BY IDDocument) AS b ON a.IDDocument = b.IDDocument INNER JOIN
                      dbo.Parteneri ON a.IDPartener = dbo.Parteneri.IDPartener

GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_FiltreazaRaportFacturi]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_FiltreazaRaportFacturi]
GO


CREATE PROCEDURE [dbo].[fares_FiltreazaRaportFacturi] 
	-- Add the parameters for the stored procedure here
	@data1 varchar(20),
	@data2 varchar(20),
	@datasold varchar(20),
	@filtru varchar(500)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @sel varchar(8000)
	SET @sel = 'SELECT * FROM (SELECT     a.IDPartener, a.NrDoc, a.DataDoc, ROUND(a.Total, 2) AS Total, COALESCE (b.Valoare, 0) AS Valoare, ROUND(a.Total, 2) - COALESCE (b.Valoare, 0) AS RestPlata, 
                      a.DataScadenta, dbo.Parteneri.Denumire, a.IDTipDocument, a.CodUnitate, cast(a.IDDocument AS Varchar(50)) AS IDDocument
		FROM         dbo.Documente AS a LEFT OUTER JOIN
                          (SELECT     IDDocument, SUM(Valoare) AS Valoare
                            FROM          dbo.DocumentePlataDetaliu dpd JOIN DocumentePlata dp ON dpd.IDDocumentPlata=dp.IDDocumentPlata WHERE Data<='''+@datasold+'''
                            GROUP BY IDDocument ) AS b ON a.IDDocument = b.IDDocument INNER JOIN
                      dbo.Parteneri ON a.IDPartener = dbo.Parteneri.IDPartener) a WHERE ' + @filtru +
	 ' AND [DataDoc]>=''' + @data1 + ''' AND [DataDoc]<=''' + @data2 +''' ORDER BY [DataDoc]'
	EXEC(@sel)
END

GO


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Articole' AND COLUMN_NAME = 'ContVenit')
BEGIN
	ALTER TABLE dbo.Articole ADD ContVenit dbo.TipCont NULL
END
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[fares_TipDocument]'))
DROP VIEW [dbo].[fares_TipDocument]
GO

CREATE VIEW [dbo].[fares_TipDocument]
AS
SELECT     Denumire + '(' + TipOperatie + ')' AS Denumire, TipOperatie, SePlateste, IDTipDocument
FROM         dbo.TipDocument

GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'FaresSetup' AND COLUMN_NAME = 'FolosesteObservatii')
BEGIN
	ALTER TABLE dbo.FaresSetup ADD FolosesteObservatii bit NOT NULL CONSTRAINT DF_FaresSetup_FolosesteObservatii DEFAULT 1
END
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[fares_Plati]'))
DROP VIEW [dbo].[fares_Plati]
GO

CREATE VIEW [dbo].[fares_Plati]
AS
SELECT     dbo.DocumentePlata.Numar, dbo.DocumentePlata.Data, dbo.DocumentePlata.CodUnitate, dbo.DocumentePlata.TipOperatiune, dbo.DocumentePlata.IDTipPlata, 
                      dbo.DocumentePlata.IDPartener, dbo.DocumentePlata.Reprezentand, dbo.DocumentePlata.DataExtras, dbo.Parteneri.Denumire, dbo.DocumentePlataDetaliu.Valoare, 
                      dbo.Documente.NrDoc, dbo.Documente.DataDoc, dbo.Documente.Total, dbo.TipDocumentPlata.Denumire AS TipDoc, dbo.DocumentePlata.IDDocumentPlata, 
                      dbo.Documente.IDDocument
FROM         dbo.DocumentePlata INNER JOIN
                      dbo.DocumentePlataDetaliu ON dbo.DocumentePlata.IDDocumentPlata = dbo.DocumentePlataDetaliu.IDDocumentPlata INNER JOIN
                      dbo.Parteneri ON dbo.DocumentePlata.IDPartener = dbo.Parteneri.IDPartener LEFT OUTER JOIN
                      dbo.Documente ON dbo.DocumentePlataDetaliu.IDDocument = dbo.Documente.IDDocument AND dbo.Parteneri.IDPartener = dbo.Documente.IDPartener INNER JOIN
                      dbo.TipDocumentPlata ON dbo.DocumentePlata.IDTipPlata = dbo.TipDocumentPlata.IDTipPlata

GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_FiltreazaRaportPlati]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_FiltreazaRaportPlati]
GO

CREATE PROCEDURE [dbo].[fares_FiltreazaRaportPlati] 
	-- Add the parameters for the stored procedure here
	@data1 varchar(20),
	@data2 varchar(20),
	@filtru varchar(500)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @sel varchar(2000)
	SET @sel = 'SELECT * FROM [fares_Plati] WHERE ' + @filtru +
	 ' AND [Data]>=''' + @data1 + ''' AND [Data]<=''' + @data2 +''' ORDER BY [Data]'
	EXEC(@sel)
END
GO


IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[fares_DocumenteDimConturi]'))
DROP VIEW [dbo].[fares_DocumenteDimConturi]
GO

CREATE VIEW [dbo].[fares_DocumenteDimConturi]
AS
SELECT     t1.NrDoc, t1.DataDoc, t1.CodUnitate, t1.valoare1, t1.valoare2, t1.valoare3, t1.valoare4, t1.DenArticol, t1.ContDebit, t1.ContCredit, t1.Valoare, t1.Denumire, t1.csd_cont, 
                      t1.csc_cont, t1.IDTipDocument, t1.IDPartener, t1.IDArticol, dbo.GetSintetic(t1.ContDebit) AS SinteticContDebit, dbo.GetSintetic(t1.ContCredit) AS SinteticContCredit, 
                      dbo.GetOrderString(t1.ContDebit) AS OrderContDebit, dbo.GetOrderString(t1.ContCredit) AS OrderContCredit, c1.Denumire AS DenDebit, c2.Denumire AS DenCredit
FROM         (SELECT     a.NrDoc, a.DataDoc, a.CodUnitate, c.valoare1, c.valoare2, c.valoare3, c.valoare4, ar.Denumire AS DenArticol, 
                                              CASE WHEN nc.ContDebit = '{ART.CNT}' THEN ar.Cont WHEN nc.ContDebit = '{TVA.DED}' THEN csd.Cont WHEN nc.ContDebit = '{TVA.COL}' THEN csc.Cont WHEN
                                               nc.ContDebit = '{PRT.CCL}' THEN p.ContClient WHEN nc.ContDebit = '{PRT.CNT}' THEN p.Cont WHEN nc.ContDebit = '{ART.CLT}' THEN ar.ContCheltuiala WHEN
                                               nc.ContDebit = '{ART.VEN}' THEN ar.ContVenit ELSE 'N/A' END AS ContDebit, 
                                              CASE WHEN nc.ContCredit = '{PRT.CNT}' THEN p.Cont WHEN nc.ContCredit = '{PRT.CCL}' THEN p.ContClient WHEN nc.ContCredit = '{TVA.DED}' THEN csd.Cont
                                               WHEN nc.ContCredit = '{TVA.COL}' THEN csc.Cont WHEN nc.ContCredit = '{ART.CLT}' THEN ar.ContCheltuiala WHEN nc.ContCredit = '{ART.VEN}' THEN ar.ContVenit
                                               WHEN nc.ContCredit = '{ART.CNT}' THEN ar.Cont ELSE 'N/A' END AS ContCredit, 
                                              CASE WHEN nc.Valoare = '{DOC.VAL}' THEN dd.ValoareTotala WHEN nc.Valoare = '{DOC.TVA}' THEN dd.ValoareTVA WHEN nc.Valoare = '{DOC.VFTVA}' THEN
                                               dd.ValoareTotala - dd.ValoareTVA WHEN nc.Valoare = '{DOC.PVA}' THEN dd.ValoareTotalaCuAdaos WHEN nc.Valoare = '{DOC.PVAVAL}' THEN (dd.ValoareTotalaCuAdaos
                                               * 100) / (100 + dd.CotaTVA) WHEN nc.Valoare = '{DOC.PVAADS}' THEN (dd.ValoareTotalaCuAdaos * 100) / (100 + dd.CotaTVA) 
                                              - dd.ValoareTotala ELSE 0 END AS Valoare, td.Denumire, csd.Cont AS csd_cont, csc.Cont AS csc_cont, td.IDTipDocument, p.IDPartener, ar.IDArticol
                       FROM          dbo.Documente AS a INNER JOIN
                                              dbo.Parteneri AS p ON a.IDPartener = p.IDPartener INNER JOIN
                                              dbo.TipDocument AS td ON a.IDTipDocument = td.IDTipDocument LEFT OUTER JOIN
                                              dbo.fares_DocumenteDimensiuniLinie AS c ON a.IDDocument = c.IDCheieTabela AND a.CodUnitate = c.CodUnitate INNER JOIN
                                              dbo.DocumenteDetaliu AS dd ON a.IDDocument = dd.IDDocument INNER JOIN
                                              dbo.Articole AS ar ON dd.IDArticol = ar.IDArticol INNER JOIN
                                              dbo.GenNoteContabile AS nc ON nc.IDTipDocument = td.IDTipDocument LEFT OUTER JOIN
                                              dbo.ConturiSpeciale AS csd ON csd.CodUnitate = a.CodUnitate AND csd.Tipuri LIKE '%' + nc.ContDebit + '%' LEFT OUTER JOIN
                                              dbo.ConturiSpeciale AS csc ON csc.CodUnitate = a.CodUnitate AND csc.Tipuri LIKE '%' + nc.ContCredit + '%') AS t1 LEFT OUTER JOIN
                      dbo.Conturi AS c1 ON c1.Cont = t1.ContDebit AND c1.CodUnitate = t1.CodUnitate LEFT OUTER JOIN
                      dbo.Conturi AS c2 ON c2.Cont = t1.ContCredit AND c2.CodUnitate = t1.CodUnitate

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_GenereazaJurnalVertical]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_GenereazaJurnalVertical]
GO

CREATE PROCEDURE [dbo].[fares_GenereazaJurnalVertical] 
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint, 
	@DeLaData datetime,
	@PanaLaData datetime,
	@cumparari smallint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT NrDoc,DataDoc,doc.CodUnitate,part.CUI,part.Denumire,part.AtributFiscal,
docd.*,rf.Denumire AS DenRegim
FROM 
Documente doc JOIN Parteneri part ON doc.IDPartener = part.IDPartener
JOIN TipDocument tipd ON doc.IDTipDocument = tipd.IDTipDocument
JOIN 

(SELECT dd.IDDocument,SUM(CASE WHEN IDTipDocument in (10,11) THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA) ValoareTotalaSiTVA,
  sum(CASE WHEN IDTipDocument in (10,11) THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END)  Baza,
  SUM(ValoareTVA) As ValoareTVA,
  dd.CotaTVA,dd.IDRegimFiscal
   FROM DocumenteDetaliu dd JOIN Documente d ON dd.IDDocument=d.IDDocument
   GROUP BY dd.IDDocument,dd.CotaTVA,dd.IDRegimFiscal) docd ON doc.IDDocument = docd.IDDocument
JOIN RegimFiscal rf ON doc.IDRegimFiscal = rf.IDRegimFiscal   
WHERE ((@cumparari=1 AND tipd.IDTipDocument in (0,10)) OR (@cumparari=0 AND tipd.IDTipDocument in (1,11))) AND doc.CodUnitate=@CodUnitate AND 
doc.DataDoc>=@DeLaData AND doc.DataDoc<=@PanaLaData
   ORDER BY DataDoc
END

GO



IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[fares_viewSumeCategorii]'))
DROP VIEW [dbo].[fares_viewSumeCategorii]
GO

CREATE VIEW [dbo].[fares_viewSumeCategorii]
AS
SELECT     dbo.TipCategoriiArticole.Denumire, dbo.Documente.DataDoc, SUM(dbo.DocumenteDetaliu.ValoareTotala) AS ValoareTotala, dbo.DocumenteDetaliu.CodUnitate, 
                      dbo.Articole.Denumire AS DenumireArticol, dbo.Departamente.Nume AS DenDepartament, dbo.Jurnale.Denumire AS DenumireJurnal, dbo.Documente.IDPartener, 
                      dbo.Documente.CodUnitate AS Expr1, dbo.Articole.IDArticol
FROM         dbo.Articole INNER JOIN
                      dbo.DocumenteDetaliu ON dbo.Articole.IDArticol = dbo.DocumenteDetaliu.IDArticol INNER JOIN
                      dbo.TipCategoriiArticole ON dbo.Articole.IDCategorie = dbo.TipCategoriiArticole.IDCategorie INNER JOIN
                      dbo.Documente ON dbo.DocumenteDetaliu.IDDocument = dbo.Documente.IDDocument LEFT OUTER JOIN
                      dbo.Departamente ON dbo.Documente.IDDepartament = dbo.Departamente.IDDepartament LEFT OUTER JOIN
                      dbo.Jurnale ON dbo.Departamente.IDJurnal = dbo.Jurnale.IDJurnal
WHERE     (dbo.Documente.IDTipDocument = 9)
GROUP BY dbo.TipCategoriiArticole.Denumire, dbo.Documente.DataDoc, dbo.DocumenteDetaliu.CodUnitate, dbo.Articole.Denumire, dbo.Departamente.Nume, 
                      dbo.Jurnale.Denumire, dbo.Documente.IDPartener, dbo.Documente.CodUnitate, dbo.Articole.IDArticol

GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_FiltreazaRaportArticoleComanda]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_FiltreazaRaportArticoleComanda]
GO

CREATE PROCEDURE [dbo].[fares_FiltreazaRaportArticoleComanda] 
	-- Add the parameters for the stored procedure here
	@data1 varchar(20),
	@data2 varchar(20),
	@filtru varchar(500)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @sel varchar(2000)
	SET @sel = 'SELECT * FROM [fares_viewSumeCategorii] WHERE ' + @filtru +
	 ' AND [DataDoc]>=''' + @data1 + ''' AND [DataDoc]<=''' + @data2 +''' ORDER BY [DataDoc]'
	EXEC(@sel)
END


GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Unitati' AND COLUMN_NAME = 'Telefon')
BEGIN
	ALTER TABLE dbo.Unitati ADD
		Telefon varchar(50) NULL,
		Fax varchar(50) NULL,
		Reprezentant varchar(150) NULL,
		Email varchar(150) NULL
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'FaresSetup' AND COLUMN_NAME = 'CaleD394')
BEGIN
	ALTER TABLE dbo.FaresSetup ADD
	CaleD394 varchar(250) NULL
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_GenereazaD394Facturi]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_GenereazaD394Facturi]
GO

CREATE PROCEDURE [dbo].[fares_GenereazaD394Facturi]
	@CodUnitate smallint,
	@DataStart datetime,
	@DataEnd datetime,
	@cumparari bit	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT b.CUI,a.Total,A.TotalValoare,a.TotalValoareTVA FROM [Documente] a JOIN Parteneri b ON a.IDPartener = b.IDPartener WHERE a.[CodUnitate]=@CodUnitate AND
	DataDoc>= @DataStart AND DataDoc<=@DataEnd  AND RTRIM(LTRIM(b.[AtributFiscal]))='RO'
	AND ((@cumparari=1 AND a.IDTipDocument=0) OR (@cumparari=0 AND a.IDTipDocument=1))
END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_GenereazaD394Parteneri]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_GenereazaD394Parteneri]
GO

CREATE PROCEDURE [dbo].[fares_GenereazaD394Parteneri]
	@CodUnitate smallint,
	@DataStart datetime,
	@DataEnd datetime
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT DISTINCT b.CUI,B.Denumire FROM [Documente] a JOIN Parteneri b ON a.IDPartener = b.IDPartener WHERE a.[CodUnitate]=@CodUnitate AND
	DataDoc>= @DataStart AND DataDoc<=@DataEnd  AND RTRIM(LTRIM(b.[AtributFiscal]))='RO'

END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_GenereazaCodArticol]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_GenereazaCodArticol]
GO

CREATE PROCEDURE [dbo].[fares_GenereazaCodArticol]
	-- Add the parameters for the stored procedure here
	@radacina varchar(10),
	@codNou varchar(20) out
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @CodReturnat varchar(20)
	DECLARE @CodReturnatNumar varchar(20)
	DECLARE @CodReturnatNumarFilt varchar(20)
	DECLARE @valNumar decimal(18,0)
	DECLARE @i int
    -- Insert statements for procedure here
	SELECT @CodReturnat=Max(codarticol) from articole where codarticol like @radacina + '%'
	--PRINT('test:[' + @CodReturnat+ ']')
	if @CodReturnat is null
	begin 
		SET @CodReturnat = @radacina + '001'
	end
	else
	begin
		SET @CodReturnat = SUBSTRING(@CodReturnat,0,10)
		--PRINT('@CodReturnat:[' + @CodReturnat+ ']')
		SET @CodReturnatNumar = REPLACE(@CodReturnat,@radacina,'')
		--PRINT('@CodReturnatNumar:[' + @CodReturnatNumar+ ']')
		SET @valNumar = 0
		SET @i = 1
		SET @CodReturnatNumarFilt = ''
		--PRINT('Inainte de while')
		WHILE @i<= LEN(@CodReturnatNumar)
		BEGIN
			IF SUBSTRING(@CodReturnatNumar,@i,1) >='0' AND SUBSTRING(@CodReturnatNumar,@i,1)<='9'
				SET @CodReturnatNumarFilt = @CodReturnatNumarFilt + SUBSTRING(@CodReturnatNumar,@i,1)
			SET @i=@i+1
		END
		
		--PRINT('@CodReturnatNumarFilt:[' + @CodReturnatNumarFilt+ ']')
		SET @valNumar = CAST(@CodReturnatNumarFilt AS decimal(20,0))
		SET @valNumar = @valNumar + 1
		
		--PRINT('@valNumar:[' + @valNumar+ ']')
		SET @CodReturnat = CAST(@valNumar as varchar)
		--PRINT('@CodReturnat:[' + @CodReturnat+ ']')
		SET @CodReturnat = @radacina + REPLICATE('0',3-LEN(@CodReturnat)) + @CodReturnat
		--PRINT('@CodReturnat:[' + @CodReturnat+ ']')
		WHILE EXISTS(SELECT * from articole where codarticol = @CodReturnat)
		BEGIN
			SET @valNumar = @valNumar + 1
			SET @CodReturnat = CAST(@valNumar as varchar)
			SET @CodReturnat = @radacina + REPLICATE('0',3-LEN(@CodReturnat)) + @CodReturnat
		END

	end
	SET @codNou = @CodReturnat
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_GenRegistruCasa]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[balcon_GenRegistruCasa]
GO

CREATE PROCEDURE [dbo].[balcon_GenRegistruCasa]
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint, 
	@DeLaData datetime,
	@PanaLaData datetime,
	@Cod varchar(50),
	@DeLaCont dbo.TipCont,
	@PanaLaCont dbo.TipCont
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	IF LEN(@Cod)=0
		SELECT ct.CodUnitate,ct.DebitInitial,ct.CreditInitial,rd.DataDoc, 
		ct.DebitInitial - ct.CreditInitial + 
		(SELECT COALESCE(SUM(rlj.ValoareLei),0) FROM Rulaje rlj JOIN RulajeDoc rjd ON rlj.IDDocument = rjd.IDDocument
		WHERE rlj.CodUnitate = ct.CodUnitate AND (rlj.ContDebit = ct.Cont) AND rjd.DataDoc<rd.DataDoc )
		-(SELECT COALESCE(SUM(rlj.ValoareLei),0) FROM Rulaje rlj JOIN RulajeDoc rjd ON rlj.IDDocument = rjd.IDDocument
		WHERE rlj.CodUnitate = ct.CodUnitate AND (rlj.ContCredit = ct.Cont) AND rjd.DataDoc<rd.DataDoc ) 
		 sodlzi,
		SUM(CASE WHEN ruld.ContDebit=ct.Cont THEN ruld.ValoareLei ELSE 0 END) rulajDebit,
		SUM(CASE WHEN ruld.ContCredit=ct.Cont THEN ruld.ValoareLei ELSE 0 END) rulajCredit
		FROM [Conturi] ct 
		JOIN [Rulaje] ruld ON ct.CodUnitate=ruld.CodUnitate AND (ruld.ContDebit = ct.Cont OR ruld.ContCredit=ct.Cont)
		JOIN [RulajeDoc] rd ON ruld.IDDocument = rd.IDDocument
		WHERE ct.CodUnitate = @CodUnitate AND rd.DataDoc<=@PanaLaData AND 
		rd.DataDoc>=@DeLaData AND 
		(LEN(@DeLaCont)=0 OR ct.Cont >= @DeLaCont) AND (LEN(@PanaLaCont) = 0 OR ct.Cont<=@PanaLaCont)
		GROUP BY ct.CodUnitate,ct.DebitInitial,ct.CreditInitial,rd.DataDoc,ct.Cont
	ELSE
			SELECT ct.CodUnitate,ct.DebitInitial,ct.CreditInitial,rd.DataDoc, 
		ct.DebitInitial - ct.CreditInitial + 
		(SELECT COALESCE(SUM(rlj.ValoareLei),0) FROM Rulaje rlj JOIN RulajeDoc rjd ON rlj.IDDocument = rjd.IDDocument
		WHERE rlj.CodUnitate = ct.CodUnitate AND (rlj.ContDebit = ct.Cont) AND rjd.DataDoc<rd.DataDoc )
		-(SELECT COALESCE(SUM(rlj.ValoareLei),0) FROM Rulaje rlj JOIN RulajeDoc rjd ON rlj.IDDocument = rjd.IDDocument
		WHERE rlj.CodUnitate = ct.CodUnitate AND (rlj.ContCredit = ct.Cont) AND rjd.DataDoc<rd.DataDoc ) 
		 sodlzi,
		SUM(CASE WHEN ruld.ContDebit=ct.Cont THEN ruld.ValoareLei ELSE 0 END) rulajDebit,
		SUM(CASE WHEN ruld.ContCredit=ct.Cont THEN ruld.ValoareLei ELSE 0 END) rulajCredit
		FROM [Conturi] ct 
		JOIN [ConturiSpeciale] cs ON ct.Cont = cs.Cont AND ct.CodUnitate = cs.CodUnitate AND cs.Tipuri LIKE @Cod
		JOIN [Rulaje] ruld ON ct.CodUnitate=ruld.CodUnitate AND (ruld.ContDebit = ct.Cont OR ruld.ContCredit=ct.Cont)
		JOIN [RulajeDoc] rd ON ruld.IDDocument = rd.IDDocument
		WHERE ct.CodUnitate = @CodUnitate AND rd.DataDoc<=@PanaLaData AND rd.DataDoc>=@DeLaData
		GROUP BY ct.CodUnitate,ct.DebitInitial,ct.CreditInitial,rd.DataDoc,ct.Cont
		
END
GO

UPDATE t set  data=CAST(CONVERT(varchar(10),data,120) as datetime) from DocumentePlata t
where '00:00:00'<>convert(varchar(8),data,8)
GO

UPDATE t set  [DataScadenta]=CAST(CONVERT(varchar(10),[DataScadenta],120) as datetime) from [Documente] t
where '00:00:00'<>convert(varchar(8),[DataScadenta],8)
GO


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'FaresSetup' AND COLUMN_NAME = 'AfiseazaDoarDocumenteleCurente')
BEGIN
ALTER TABLE dbo.FaresSetup ADD
	AfiseazaDoarDocumenteleCurente bit NOT NULL CONSTRAINT DF_FaresSetup_AfiseazaDoarDocumenteleCurente DEFAULT 0
END
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[fares_FisaPartenerului]'))
DROP VIEW [dbo].[fares_FisaPartenerului]
GO

CREATE VIEW [dbo].[fares_FisaPartenerului]
AS
SELECT     dbo.TipDocument.Denumire AS TipDocument, dbo.Documente.NrDoc, dbo.Documente.DataDoc, dbo.Documente.Total AS Obligatie, 0 AS Plata, NULL AS DataExtras, 
                      dbo.Documente.IDDocument, dbo.Documente.CodUnitate, dbo.Documente.IDPartener, p.Denumire AS denPartener, dbo.TipDocument.IDTipDocument
FROM         dbo.Documente INNER JOIN
                      dbo.TipDocument ON dbo.Documente.IDTipDocument = dbo.TipDocument.IDTipDocument JOIN
                      Parteneri p ON dbo.Documente.IDPartener = p.IDPartener
UNION
SELECT     b.Denumire AS TipDocument, Numar AS NrDoc, Data AS DataDoc, 0 AS Obligatie, ValoareTotala AS Plata, a.Dataextras, IDDocument, a.codUnitate, a.IDPartener, 
                      p.Denumire AS denPartener,b.IDTipPlata+1000 AS IDTipDocument
FROM         DocumentePlata a JOIN
                      TipDocumentPlata b ON a.IDTipPlata = b.IDTipPlata JOIN
                      DocumentePlataDetaliu c ON c.IDDocumentPlata = a.IDDocumentPlata JOIN
                      Parteneri p ON a.IDPartener = p.IDPartener

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_FiltreazaRaportFisaPartenerului]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_FiltreazaRaportFisaPartenerului]
GO

CREATE PROCEDURE [dbo].[fares_FiltreazaRaportFisaPartenerului] 
	@data2 varchar(20),
	@filtru varchar(5000)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @sel varchar(2000)
	SET @sel = 'SELECT * FROM [fares_FisaPartenerului] WHERE ' + @filtru +
	 ' AND [DataDoc]<=''' + @data2 +''' ORDER BY [DataDoc]'
	EXEC(@sel)
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_StergeUnitate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[balcon_StergeUnitate]
GO

CREATE PROCEDURE [dbo].[balcon_StergeUnitate] 
 @CodUnitate smallint AS
BEGIN
DECLARE @NrDoc AS int
SET @NrDoc = 0

BEGIN TRAN

DELETE FROM [ParamListe] WHERE [CodUnitate] = @CodUnitate AND [Host]<>'0'
IF @@ERROR <>0
    GOTO Eroare 


DELETE FROM [Arhive] WHERE [CodUnitate] = @CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

DELETE FROM [Rulaje] WHERE [CodUnitate] = @CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

DELETE FROM [RulajeDoc] WHERE [CodUnitate] = @CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

DELETE FROM [BalconSetup] WHERE [CodUnitate] = @CodUnitate
IF @@ERROR <>0
    GOTO Eroare 
DELETE FROM [SeriiNumerice] WHERE [CodUnitate] = @CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

---initializare bilant           
DELETE FROM [Bilant] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

DELETE FROM [ConturiSpeciale]WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 
DELETE FROM [ArticoleAliniate] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

---Conturi corespondente
DELETE FROM [ConturiCoresp]WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

--conturi
DELETE FROM [Conturi] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

           
DELETE FROM [Jurnale] WHERE [CodUnitate] = @CodUnitate
IF @@ERROR <>0
    GOTO Eroare 


DELETE FROM [RezultatPatrimonial] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 
          
DELETE FROM [VenituriSiCheltuieli] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

DELETE FROM [ConturiSpeciale] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

COMMIT TRAN    
GOTO Gata
Eroare:
  ROLLBACK
  RAISERROR ('Eroare la executia procedurii StergeUnitate',-1,0)
Exista_Documente:
  ROLLBACK
  RAISERROR ('Unitatea nu poate fi stearsa deoarece contine documente!',-1,0)
Exista_Rulaje_Documente:
  ROLLBACK
  RAISERROR ('Unitatea nu poate fi stearsa deoarece contine rulaje - documente!',-1,0)

Gata:

END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_StergeUnitate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_StergeUnitate]
GO

CREATE PROCEDURE [dbo].[fares_StergeUnitate] 
 @CodUnitate smallint AS
BEGIN
DECLARE @NrDoc AS int
SET @NrDoc = 0

BEGIN TRAN

PRINT('DocumentePlataDetaliu')
DELETE FROM [DocumentePlataDetaliu] WHERE IDDocumentPlata IN (SELECT IDDocumentPlata FROM [DocumentePlata] WHERE  [CodUnitate]=@CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 

PRINT('DocumentePlata')
DELETE FROM [DocumentePlata] WHERE  [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 



PRINT('DocumenteDetaliu')
DELETE FROM [DocumenteDetaliu] WHERE IDDocument IN (SELECT IDDocument FROM [Documente] WHERE  [CodUnitate]=@CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 

PRINT('[ArticoleParteneri]')
DELETE FROM [ArticoleParteneri] WHERE IDArticol IN (SELECT IDArticol FROM [articole] WHERE [CodUnitate]=@CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 


PRINT('[ArticoleVariante]')
DELETE FROM [ArticoleVariante] WHERE IDArticol IN (SELECT IDArticol FROM [articole] WHERE [CodUnitate]=@CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 

PRINT('[UMConversie]')
DELETE FROM [UMConversie] WHERE IDArticol IN (SELECT IDArticol FROM [articole] WHERE [CodUnitate]=@CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 


PRINT('Articole')
DELETE FROM [articole] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 


PRINT('Documente')
DELETE FROM [Documente] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare
     
PRINT('ArticoleParteneri')
DELETE FROM [ArticoleParteneri] WHERE IDPartener IN (SELECT IDPartener FROM [Parteneri] WHERE [CodUnitate]=@CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 
    
PRINT('[ParteneriConturiBancare]')
DELETE FROM [ParteneriConturiBancare] WHERE IDPartener IN (SELECT IDPartener FROM [Parteneri] WHERE [CodUnitate]=@CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 

    
PRINT('Parteneri')
DELETE FROM [Parteneri] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

DELETE FROM [Stocuri] WHERE IDArticol in (SELECT IDArticol FROM [articole] WHERE [CodUnitate]=@CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 



PRINT 'FaresSetup'
DELETE FROM [FaresSetup] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

PRINT 'Trasee'
DELETE FROM [Trasee] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

PRINT 'ValoriDimensiuni'
DELETE FROM [ValoriDimensiuni] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 


COMMIT TRAN    
GOTO Gata
Eroare:
  ROLLBACK
  RAISERROR ('Eroare la executia procedurii StergeUnitate',-1,0)
Exista_Documente:
  ROLLBACK
  RAISERROR ('Unitatea nu poate fi stearsa deoarece contine documente!',-1,0)
Exista_Rulaje_Documente:
  ROLLBACK
  RAISERROR ('Unitatea nu poate fi stearsa deoarece contine rulaje - documente!',-1,0)

Gata:

END
GO

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ConcediiMedicale' AND COLUMN_NAME = 'IdAngajat') AND
	NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ConcediiMedicale' AND COLUMN_NAME = 'IdPersoana')
BEGIN
	EXECUTE sp_rename N'dbo.ConcediiMedicale.IdAngajat', N'Tmp_IdPersoana', 'COLUMN' 
	EXECUTE sp_rename N'dbo.ConcediiMedicale.Tmp_IdPersoana', N'IdPersoana', 'COLUMN' 
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'PosturiVacante' AND COLUMN_NAME = 'CodUnitate') 
BEGIN
	ALTER TABLE dbo.PosturiVacante ADD
		CodUnitate smallint NOT NULL CONSTRAINT DF_PosturiVacante_CodUnitate DEFAULT 0

	ALTER TABLE dbo.PosturiVacante ADD CONSTRAINT
		FK_PosturiVacante_Unitati FOREIGN KEY
		(
		CodUnitate
		) REFERENCES dbo.Unitati
		(
		CodUnitate
		) ON UPDATE  NO ACTION 
		 ON DELETE  NO ACTION 
END	
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[resuma_StergeUnitate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[resuma_StergeUnitate]
GO

CREATE PROCEDURE [dbo].[resuma_StergeUnitate] 
 @CodUnitate smallint AS
BEGIN
DECLARE @NrDoc AS int
SET @NrDoc = 0

BEGIN TRAN

PRINT 'Departamente'
DELETE FROM [ContracteMunca] WHERE [CodUnitate] = @CodUnitate
IF @@ERROR <>0
    GOTO Eroare 
    
PRINT '[AngajatiAdaosuri]'
DELETE FROM [AngajatiAdaosuri] WHERE [IDPersoana] IN (SELECT IDPersoana FROM [Persoane] WHERE [CodUnitate] = @CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 
PRINT '[AngajatiOcupatii]'
DELETE FROM [AngajatiOcupatii] WHERE [IDPersoana] IN (SELECT IDPersoana FROM [Persoane] WHERE [CodUnitate] = @CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 
PRINT '[AngajatiPontaj]'
DELETE FROM [AngajatiPontaj] WHERE [IDPersoana] IN (SELECT IDPersoana FROM [Persoane] WHERE [CodUnitate] = @CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 

PRINT '[AngajatiSporuri]'
DELETE FROM [AngajatiSporuri] WHERE [IDPersoana] IN (SELECT IDPersoana FROM [Persoane] WHERE [CodUnitate] = @CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 

PRINT '[AngajatiSalarizare]'
DELETE FROM [AngajatiSalarizare] WHERE [IDPersoana] IN (SELECT IDPersoana FROM [Persoane] WHERE [CodUnitate] = @CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 

PRINT '[ConcediiMedicale]'
DELETE FROM [ConcediiMedicale] WHERE [IDPersoana] IN (SELECT IDPersoana FROM [Persoane] WHERE [CodUnitate] = @CodUnitate)
IF @@ERROR <>0
   GOTO Eroare 

PRINT '[ContracteMunca]'
DELETE FROM [ContracteMunca] WHERE [IDPersoana] IN (SELECT IDPersoana FROM [Persoane] WHERE [CodUnitate] = @CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 
    
PRINT '[PersoaneSuspCM]'
DELETE FROM PersoaneSuspCM WHERE [IDPersoana] IN (SELECT IDPersoana FROM [Persoane] WHERE [CodUnitate] = @CodUnitate)
IF @@ERROR <>0
    GOTO Eroare     

PRINT '[SituatieMilitara]'
DELETE FROM [SituatieMilitara] WHERE [IDPersoana] IN (SELECT IDPersoana FROM [Persoane] WHERE [CodUnitate] = @CodUnitate)
IF @@ERROR <>0
    GOTO Eroare  
    
PRINT '[SpecialitatiPersoane]'
DELETE FROM [SpecialitatiPersoane] WHERE [IDPersoana] IN (SELECT IDPersoana FROM [Persoane] WHERE [CodUnitate] = @CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 

PRINT '[CalculSporuri]'
DELETE FROM [CalculSporuri] WHERE [IDPersoana] IN (SELECT IDPersoana FROM [Persoane] WHERE [CodUnitate] = @CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 

PRINT '[RaportareFoiBoala]'
DELETE FROM [RaportareFoiBoala] WHERE [IDPersoana] IN (SELECT IDPersoana FROM [Persoane] WHERE [CodUnitate] = @CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 



PRINT '[Persoane]'
DELETE FROM [Persoane] WHERE [CodUnitate] = @CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

PRINT '[PosturiVacante]'
DELETE FROM [PosturiVacante] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

PRINT '[ResumaSetup]'
DELETE FROM [ResUmaSetup] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 



COMMIT TRAN    
GOTO Gata
Eroare:
  ROLLBACK
  RAISERROR ('Eroare la executia procedurii StergeUnitate',-1,0)
Exista_Documente:
  ROLLBACK
  RAISERROR ('Unitatea nu poate fi stearsa deoarece contine documente!',-1,0)
Exista_Rulaje_Documente:
  ROLLBACK
  RAISERROR ('Unitatea nu poate fi stearsa deoarece contine rulaje - documente!',-1,0)

Gata:

END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[exbug_StergeUnitate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[exbug_StergeUnitate]
GO

CREATE PROCEDURE [dbo].[exbug_StergeUnitate] 
 @CodUnitate smallint AS
BEGIN
DECLARE @NrDoc AS int
SET @NrDoc = 0

BEGIN TRAN





    
PRINT '[AngajamentLegalDetaliu]'    
DELETE FROM AngajamentLegalDetaliu WHERE IDAL IN (SELECT IDAL FROM AngajamentLegal WHERE [CodUnitate]=@CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 

PRINT '[AngajamentLegal]'    
DELETE FROM [AngajamentLegal] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

PRINT '[AngajamentBugetarDetaliu]'    
DELETE FROM [AngajamentBugetarDetaliu] WHERE IDAB IN (SELECT IDAB FROM AngajamentBugetar WHERE [CodUnitate]=@CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 

PRINT '[AngajamentBugetarNC]'    
DELETE FROM [AngajamentBugetarNC] WHERE IDAB IN (SELECT IDAB FROM AngajamentBugetar WHERE [CodUnitate]=@CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 

PRINT '[AngajamentBugetar]'    
DELETE FROM [AngajamentBugetar] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

PRINT 'Ordonantari'    
DELETE FROM Ordonantari WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

PRINT 'PropunereAngajareCheltuialaDetaliu'    
DELETE FROM PropunereAngajareCheltuialaDetaliu WHERE IDPac IN (SELECT IDPac FROM PropunereAngajareCheltuiala WHERE [CodUnitate]=@CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 

PRINT '[PropunereAngajareCheltuiala]'    
DELETE FROM [PropunereAngajareCheltuiala] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

PRINT 'ArticoleAliniate'    
DELETE FROM ArticoleAliniate WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

PRINT 'ResponsabilVizaCFP'
DELETE FROM [ResponsabilVizaCFP] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 
    
COMMIT TRAN    
GOTO Gata
Eroare:
  ROLLBACK
  RAISERROR ('Eroare la executia procedurii StergeUnitate',-1,0)
Exista_Documente:
  ROLLBACK
  RAISERROR ('Unitatea nu poate fi stearsa deoarece contine documente!',-1,0)
Exista_Rulaje_Documente:
  ROLLBACK
  RAISERROR ('Unitatea nu poate fi stearsa deoarece contine rulaje - documente!',-1,0)

Gata:

END


GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[amix_StergeUnitate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[amix_StergeUnitate]
GO

CREATE PROCEDURE [dbo].[amix_StergeUnitate] 
 @CodUnitate smallint AS
BEGIN
DECLARE @NrDoc AS int
SET @NrDoc = 0

BEGIN TRAN
PRINT('[AmixSetup]')
DELETE FROM [AmixSetup] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 
PRINT('[AmixAmortizare]')
DELETE FROM [AmixAmortizare] WHERE IDMijlocFix in (SELECT IDMijlocFix FROM [AmixRegistru] WHERE [CodUnitate]=@CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 
PRINT('[AmixAnsamble]')
DELETE FROM [AmixAnsamble] WHERE IDMFixPrincipal in (SELECT IDMijlocFix FROM [AmixRegistru] WHERE [CodUnitate]=@CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 
PRINT('[AmixComponente] - nu se sterge(view)')
  
PRINT('[AmixComponenteSecundare]')
DELETE FROM [AmixComponenteSecundare] WHERE IDMijlocFix in (SELECT IDMijlocFix FROM [AmixRegistru] WHERE [CodUnitate]=@CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 
   
PRINT('[AmixDocumente]')
DELETE FROM [AmixDocumente] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare       
      
PRINT('[AmixRegistru]')
DELETE FROM [AmixRegistru] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 
    
PRINT('[AmixRegistruBK]')
DELETE FROM [AmixRegistruBK] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

PRINT('nu se sterg [AmixGestionari]')


COMMIT TRAN    
GOTO Gata
Eroare:
  ROLLBACK
  RAISERROR ('Eroare la executia procedurii StergeUnitate',-1,0)
Exista_Documente:
  ROLLBACK
  RAISERROR ('Unitatea nu poate fi stearsa deoarece contine documente!',-1,0)
Exista_Rulaje_Documente:
  ROLLBACK
  RAISERROR ('Unitatea nu poate fi stearsa deoarece contine rulaje - documente!',-1,0)

Gata:

END


GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StergeUnitate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[StergeUnitate]
GO

CREATE PROCEDURE [dbo].[StergeUnitate] 
 @CodUnitate smallint,
 @faraStergereTabelaUnitati bit
 
 
 AS
BEGIN
DECLARE @NrDoc AS int
SET @NrDoc = 0

BEGIN TRAN
IF @CodUnitate=0
	GOTO Eroare2 
EXECUTE amix_StergeUnitate @CodUnitate
IF @@ERROR <>0
    GOTO Eroare 
EXECUTE exbug_StergeUnitate @CodUnitate
IF @@ERROR <>0
    GOTO Eroare 
EXECUTE resuma_StergeUnitate @CodUnitate
IF @@ERROR <>0
    GOTO Eroare 
EXECUTE fares_StergeUnitate @CodUnitate
IF @@ERROR <>0
    GOTO Eroare 
EXECUTE balcon_StergeUnitate @CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

PRINT 'Departamente'
DELETE FROM [Departamente] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

PRINT 'SetariLocale'
DELETE FROM [SetariLocale] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 


UPDATE [Unitati] SET [UnitateSursa]=0 WHERE [UnitateSursa]=@CodUnitate

IF @faraStergereTabelaUnitati=1
BEGIN
	PRINT 'Unitati'
	DELETE FROM [Unitati] WHERE [CodUnitate]=@CodUnitate
	IF @@ERROR <>0
		GOTO Eroare 
END
    


COMMIT TRAN    
GOTO Gata
Eroare:
  ROLLBACK
  RAISERROR ('Eroare la executia procedurii StergeUnitate',-1,0)
Eroare2:
  ROLLBACK
  RAISERROR ('Unitatea demonstrativa nu se poate sterge!',-1,0)
  
Exista_Documente:
  ROLLBACK
  RAISERROR ('Unitatea nu poate fi stearsa deoarece contine documente!',-1,0)
Exista_Rulaje_Documente:
  ROLLBACK
  RAISERROR ('Unitatea nu poate fi stearsa deoarece contine rulaje - documente!',-1,0)

Gata:
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CompuneCont]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[CompuneCont]
GO

CREATE FUNCTION [dbo].[CompuneCont]
(
	-- Add the parameters for the function here
	@sintetic decimal(6,0),
	@analitic1 decimal(6,0),
	@analitic2 decimal(6,0),
	@analitic3 decimal(6,0),
	@analitic4 decimal(6,0),
	@analitic5 decimal(6,0)
	
)
RETURNS varchar(50)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @ResultVar varchar(50)
	-- Add the T-SQL statements to compute the return value here
	--SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>
    if @sintetic<>0
		set @ResultVar = LTRIM(RTRIM(cast(@sintetic as varchar(6))))
	if @analitic1<>0
		set @ResultVar = @ResultVar + '.' + LTRIM(RTRIM(cast(@analitic1 as varchar(6))))	
	if @analitic2<>0
		set @ResultVar = @ResultVar + '.' + LTRIM(RTRIM(cast(@analitic2 as varchar(6))))
	if @analitic3<>0
		set @ResultVar = @ResultVar + '.' + LTRIM(RTRIM(cast(@analitic3 as varchar(6))))
	if @analitic4<>0
		set @ResultVar = @ResultVar + '.' + LTRIM(RTRIM(cast(@analitic4 as varchar(6))))	
	if @analitic5<>0
		set @ResultVar = @ResultVar + '.' + LTRIM(RTRIM(cast(@analitic5 as varchar(6))))
	-- Return the result of the function
	RETURN @ResultVar

END

GO




IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetContParinte]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[GetContParinte]
GO

CREATE FUNCTION [dbo].[GetContParinte]
(
	-- Add the parameters for the function here
	@sintetic decimal(6,0),
	@analitic1 decimal(6,0),
	@analitic2 decimal(6,0),
	@analitic3 decimal(6,0),
	@analitic4 decimal(6,0),
	@analitic5 decimal(6,0)
	
)
RETURNS varchar(50)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @ResultVar varchar(50)
	SET @ResultVar = 'N/A'
	-- Add the T-SQL statements to compute the return value here
	--SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>
	if @analitic1<>0
	begin
		set @ResultVar = LTRIM(RTRIM(cast(@sintetic as varchar(6))))	
		if @analitic2<>0
		begin
			set @ResultVar = @ResultVar +'.'+ LTRIM(RTRIM(cast(@analitic1 as varchar(6))))
			if @analitic3<>0
			begin
				set @ResultVar = @ResultVar +'.'+ LTRIM(RTRIM(cast(@analitic2 as varchar(6))))
				if @analitic4<>0
				begin
					set @ResultVar = @ResultVar +'.'+ LTRIM(RTRIM(cast(@analitic3 as varchar(6))))	
					if @analitic5<>0
						set @ResultVar = @ResultVar +'.' +LTRIM(RTRIM(cast(@analitic4 as varchar(6))))
				end
			end
		end
	end	
	-- Return the result of the function
	RETURN @ResultVar

END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_ImportBalansW]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[balcon_ImportBalansW]
GO


CREATE PROCEDURE [dbo].[balcon_ImportBalansW]
	-- Add the parameters for the stored procedure here
	@ServerName varchar(50), 
	@DBName varchar(50),
	@CodUnitate smallint,
	@CodNod bigint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @comanda varchar(5000)
    DECLARE @strCodUnitate varchar(10)
    DECLARE @strCodNod varchar(20)
	
	IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SaveArt]') AND type in (N'U'))
		DROP TABLE [dbo].[SaveArt]

	BEGIN TRAN
	
	SET @strCodUnitate = cast(@CodUnitate as varchar(10))
	SET @strCodNod = cast(@CodNod as varchar(20))
   	
    SET @Comanda = 'DELETE FROM [ConturiSpeciale] WHERE [CodUnitate] =' + @strCodUnitate 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	

    SET @Comanda = 'DELETE FROM [ConturiCoresp] WHERE [CodUnitate] =' + @strCodUnitate 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	

    SET @Comanda = 'DELETE FROM [RulajeDoc] WHERE [CodUnitate] =' + @strCodUnitate 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	

    SET @Comanda = 'DELETE FROM [Rulaje] WHERE [CodUnitate] =' + @strCodUnitate 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	

	
	--Salveaza articolele si legatura cu conturile
	SET @Comanda = 'SELECT [IDArticol],[Cont],[ContCheltuiala],[ContVenit] INTO [SaveArt] FROM [Articole]  WHERE [CodUnitate] =' + @strCodUnitate 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	
	SET @Comanda = 'UPDATE [Articole] SET [Cont]=''N/A'',[ContCheltuiala]=''N/A'',[ContVenit]=''N/A'' WHERE [CodUnitate] =' + @strCodUnitate 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	


    SET @Comanda = 'DELETE FROM [Conturi] WHERE [CodUnitate] =' + @strCodUnitate  + ' AND [Cont]<>''N/A'''
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('Sterge Conturi - ok')

	SET @Comanda = 'INSERT INTO [Conturi]
           ([Cont],[Denumire],[Tip],[Terminal],[ContParinte],[DebitInitial],[CreditInitial],[DebitPrecedent],[CreditPrecedent],[Categorie],[RulajDebit],[RulajCredit],[CodUnitate])
		SELECT dbo.CompuneCont([Sintetic],[Analitic1],[Analitic2],[Analitic3],[Analitic4],[Analitic5]) [Cont],LTRIM(RTRIM([Denumire])) [Denumire],[Functie],[Terminal],'+
		'dbo.GetContParinte([Sintetic],[Analitic1],[Analitic2],[Analitic3],[Analitic4],[Analitic5]) [ContParinte],'+
		'[DebInit],[CreInit],[DebPrec],[CrePrec],CASE WHEN [ModelContabil]=''F'' THEN 0 WHEN [ModelContabil]=''X'' THEN 2 ELSE 3 END [Categorie],[Debit],[Credit],'+@strCodUnitate+' FROM '
	IF @ServerName <> ''
	BEGIN	
		SET @Comanda = @Comanda + '[' + @ServerName+'].'
	END	
	SET @Comanda = @Comanda +'['+@DBName+'].[dbo].[Cont] WHERE [Tip]=''S'' AND CodNod='  + @strCodNod   
    PRINT(@Comanda)
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	

	DECLARE @index smallint
	SET @index = 1

	WHILE @index<=5
	BEGIN
		SET @Comanda = 'INSERT INTO [Conturi]
			   ([Cont],[Denumire],[Tip],[Terminal],[ContParinte],[DebitInitial],[CreditInitial],[DebitPrecedent],[CreditPrecedent],[Categorie],[RulajDebit],[RulajCredit],[CodUnitate])
			SELECT dbo.CompuneCont([Sintetic],[Analitic1],[Analitic2],[Analitic3],[Analitic4],[Analitic5]),LTRIM(RTRIM([Denumire])),[Functie],[Terminal],'+
			'dbo.GetContParinte([Sintetic],[Analitic1],[Analitic2],[Analitic3],[Analitic4],[Analitic5]),'+
			'[DebInit],[CreInit],[DebPrec],[CrePrec],CASE WHEN [ModelContabil]=''F'' THEN 0 WHEN [ModelContabil]=''X'' THEN 2 ELSE 3 END [Categorie],[Debit],[Credit],'+@strCodUnitate+' FROM '
		IF @ServerName <> ''
		BEGIN	
			SET @Comanda = @Comanda + '[' + @ServerName+'].'
		END	
		SET @Comanda = @Comanda +'['+@DBName+'].[dbo].[Cont] WHERE [Tip]=''A'+CAST(@index AS varchar(1))+''' AND CodNod='  + @strCodNod   
	    
		EXEC(@Comanda) 
		IF @@ERROR != 0 GOTO EROARE
		SET @index=@index+1	
	END
	
	--,[ContCheltuiala]=a.[ContCheltuiala],[ContVenit]=a.[ContVenit]
	PRINT('Import Conturi - ok')	
	SET @Comanda = 'UPDATE [Articole] SET [Cont]=a.[Cont] FROM [SaveArt] a  WHERE a.IDArticol = Articole.IDarticol ' +
	'AND [CodUnitate] =' + @strCodUnitate  + ' AND EXISTS(SELECT * FROM [Conturi] b WHERE a.[Cont]=b.[Cont] AND [CodUnitate] =' + @strCodUnitate+' )'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	
	
	PRINT('Import Conturi Articole- ok')

    SET @Comanda = 'UPDATE [Conturi] SET [EsteAnalitic]=0 WHERE LEN(LTRIM(RTRIM([Cont])))<=4'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	

    
    SET @index = 1

	WHILE @index<=5
	BEGIN
		SET @Comanda = 'INSERT INTO [ArticoleAliniate]
			   ([ArtAl],[Denumire],[AAParinte],[Terminal],[Titlu],[Categorii],[TermDenumire],[StartDenumire],[Anexa6],[CodUnitate])
		SELECT dbo.CompuneCont([Analitic1],[Analitic2],[Analitic3],[Analitic4],[Analitic5],0),LTRIM(RTRIM(a.[Denumire])) Denumire'+
		',dbo.GetContParinte([Analitic1],[Analitic2],[Analitic3],[Analitic4],[Analitic5],0) [AAParinte],[Terminal] ,e.Denumire [Titlu],'
		
		IF @index=1
			SET @Comanda = @Comanda + '''[CLT]'''
		ELSE	
			SET @Comanda = @Comanda + '''[TOT]'''
		SET @Comanda = @Comanda +  ' [Categorii],aduna [TermDenumire],'''' [StartDenumire], 0 [Anexa6],'+@strCodUnitate+'  FROM '
		IF @ServerName <> ''
		BEGIN	
			SET @Comanda = @Comanda + '[' + @ServerName+'].'
		END	
		SET @Comanda = @Comanda + '['+@DBName+'].[dbo].[ExBCoduri] a JOIN '
		
		IF @ServerName <> ''
		BEGIN	
			SET @Comanda = @Comanda + '[' + @ServerName+'].'
		END	
		SET @Comanda = @Comanda + '['+@DBName+'].[dbo].[ExBTitluri] e ON a.CodTitlu = e.CodTitlu WHERE ' +
		 ' NOT EXISTS(SELECT * FROM [ArticoleAliniate] b WHERE dbo.CompuneCont([Analitic1],[Analitic2],[Analitic3],[Analitic4],[Analitic5],0)=b.[ArtAl] AND [CodUnitate] =' + @strCodUnitate+' )' +
		 ' AND Tip=''A'+CAST(@index AS varchar(1))+''' AND Analitic1<>0'
		PRINT(@Comanda) 
		EXEC(@Comanda) 
		IF @@ERROR != 0 GOTO EROARE
		SET @index=@index+1		
	END
	
	
	PRINT('ArticoleAliniarte - ok')
    
    /*SET @Comanda = 'SET IDENTITY_INSERT [Parteneri] ON;INSERT INTO [Parteneri]
           ([IDPartener],[Denumire],[CodFiscal],[IDBanca],[ContBancar],[Cont],[CodUnitate],[CUI])
		SELECT		[IDFurnizor],[Denumire],[CodFiscal],0,[ContBancar],[Cont],'+@strCodUnitate+',[IDFurnizor] FROM ['+@ServerName+'].['+@DBName+'].[dbo].[Furnizori] '
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('Parteneri - ok')

    SET @Comanda = 'INSERT INTO [ConturiSpeciale]
           ([Cont],[Tipuri],[Descriere],[CodUnitate])
			SELECT [Cont],[Tipuri],[Descriere],'+@strCodUnitate+' FROM ['+@ServerName+'].['+@DBName+'].[dbo].[ConturiSpeciale]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('ConturiSpeciale - ok')
	
    SET @Comanda = 'DELETE FROM [Jurnale] WHERE [CodUnitate]=' + @strCodUnitate 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	

    SET @Comanda = 'SET IDENTITY_INSERT [Parteneri] OFF'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	*/

    /*SET @Comanda = 'SET IDENTITY_INSERT [Jurnale] ON;INSERT INTO [Jurnale]
           ([IdJurnal],[Denumire]
           ,[IDParinte]
           ,[Codificare]
           ,[Terminal],[CodUnitate])
SELECT [IdJurnal],[Denumire]
           ,[IDParinte]
           ,[Codificare]
           ,[Terminal],'+@strCodUnitate+' FROM ['+@ServerName+'].['+@DBName+'].[dbo].[Jurnale] WHERE [IDJurnal]<>[IdParinte]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('Jurnale - ok')

    SET @Comanda = 'SET IDENTITY_INSERT [Jurnale] OFF'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	

    SET @Comanda = 'SET IDENTITY_INSERT [Ordonantari] ON;INSERT INTO [Ordonantari]
           ([IDOrdonantare],[NrDoc]
           ,[DataDoc]
           ,[ArtAl]
           ,[IDFurnizor]
           ,[ValoareLei]
           ,[IDJurnal]
           ,[Scop]
           ,[DocJust]
           ,[AngLegal]
           ,[Achitat],[CodUnitate])
SELECT [IDOrdonantare],[NrDoc]
           ,[DataDoc]
           ,SUBSTRING(REPLACE([Cont],''7701.'',''''),1,40)
           ,[IDFurnizor]
           ,[ValoareLei]
           ,[IDJurnal]
           ,[Scop]
           ,[DocJust]
           ,[AngLegal]
           ,[Achitat],'+@strCodUnitate+' FROM ['+@ServerName+'].['+@DBName+'].[dbo].[Ordonantari]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('Ordonantari - ok')
    SET @Comanda = 'SET IDENTITY_INSERT [Ordonantari] OFF'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	



    SET @Comanda = 'SET IDENTITY_INSERT [RulajeDoc] ON;INSERT INTO [RulajeDoc]
           ([IDDocument],[ValoareLei]
           ,[ValoareValuta]
           ,[NumarDoc]
           ,[DataDoc]
           ,[NumarNota]
           ,[TipFormula]
           ,[IDJurnal]
           ,[Observatii]
           ,[xUser]
           ,[xHost]
           ,[xDate],[CodUnitate])
SELECT [IDDocument],[ValoareLei]
           ,[ValoareValuta]
           ,[NumarDoc]
           ,[DataDoc]
           ,[NumarNota]
           ,[TipFormula]
           ,[IDJurnal]
           ,[Observatii]
           ,[xUser]
           ,[xHost]
           ,[xDate],'+@strCodUnitate+' FROM ['+@ServerName+'].['+@DBName+'].[dbo].[RulajeDoc]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('RulajeDoc - ok')
    SET @Comanda = 'SET IDENTITY_INSERT [RulajeDoc] OFF'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	

    SET @Comanda = 'SET IDENTITY_INSERT [Rulaje] ON;INSERT INTO [Rulaje]
           ([CodFormula]
      ,[ContDebit]
      ,[ContCredit]
      ,[CodValuta]
      ,[IDDocument]
      ,[ValoareValuta]
      ,[Curs]
      ,[ValoareLei]
      ,[DataDocJust]
      ,[TipDocJust]
      ,[NumarDocJust]
      ,[ExplicatiiDocJust]
      ,[NrCrt],[CodUnitate])
SELECT [CodFormula]*1000
      ,[ContDebit]
      ,[ContCredit]
      ,[CodValuta]
      ,[IDDocument]
      ,[ValoareValuta]
      ,[Curs]
      ,[ValoareLei]
      ,[DataDocJust]
      ,[TipDocJust]
      ,[NumarDocJust]
      ,[ExplicatiiDocJust]
      ,[NrCrt],'+@strCodUnitate+' FROM ['+@ServerName+'].['+@DBName+'].[dbo].[Rulaje]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('Rulaje - ok')
    SET @Comanda = 'SET IDENTITY_INSERT [Rulaje] OFF'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	

    SET @Comanda = 'DELETE FROM [RulajeDoc] WHERE NOT EXISTS(SELECT * FROM [Rulaje] a 
 WHERE a.IdDocument=[RulajeDoc].IDDocument)'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
*/
 	COMMIT



	GOTO Gata
	EROARE:
		ROLLBACK
		RAISERROR ('Eroare la preluarea datelor!',16,-1)
		GOTO Gata
	EROARE1:
		RAISERROR ('!!',16,-1)	
		GOTO Gata
	Gata:
	
END

GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'FaresSetup' AND COLUMN_NAME = 'ContVenitArticolImplicit')
BEGIN
ALTER TABLE dbo.FaresSetup ADD
	ContVenitArticolImplicit [dbo].[TipCont]  NULL 
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Semnaturi' AND COLUMN_NAME = 'LaComanda')
BEGIN
ALTER TABLE dbo.Semnaturi ADD
	LaComanda bit NOT NULL CONSTRAINT DF_Semnaturi_LaComanda DEFAULT 1,
	LaReceptie bit NOT NULL CONSTRAINT DF_Semnaturi_LaReceptie DEFAULT 1
END
GO


/****** Object:  StoredProcedure [dbo].[GenListaBalanta]    Script Date: 12/29/2010 12:23:24 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GenListaBalanta]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GenListaBalanta]
GO


CREATE PROCEDURE [dbo].[GenListaBalanta]
	@pAn smallint   
AS

DECLARE @dataUI as float, @dtDataStartUI as datetime,@dtDataEndUI as datetime
DECLARE @strContStart as TipCont,@strContEnd as TipCont
DECLARE @strDataStart as varchar(50), @strDataEnd as varchar(50)
DECLARE @tabela varchar(100)
DECLARE @categorie as tinyint
DECLARE @strcategorie as varchar(10)
DECLARE @analitica bit
DECLARE @cfm bit
DECLARE @dataStartFunctie varchar(50)
DECLARE @jurnal int
DECLARE @dataEndFunctie varchar(50)
DECLARE @ultimaData datetime
DECLARE @ptgc bit


--determina ultima data la care s-a lucrat
SELECT @ultimaData= MAX(DataDoc) FROM RulajeDoc


SET @categorie = 0
SET @analitica = 0
SET @cfm = 0
SET @ptgc = 0


DECLARE @tipb as varchar(100)
DECLARE @idLista as int;
SET @idLista = 1

DECLARE @CodUnitate smallint
SET @CodUnitate=0

SET @tipb='0'
SELECT @CodUnitate=CAST(Valoare AS smallint) FROM [SetariLocale] WHERE [CodSetare]='UCRT' AND [xUser]=suser_sname() AND [xHost]=host_name()
PRINT('CodUnitate ''' + cast(@CodUnitate as varchar(20)) + '''')
SELECT @tipb=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='TPB' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate
IF @tipb='Extra Bilantiera'
BEGIN
	SET @categorie = 2			
END

SET @tipb='0'
SELECT @tipb=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='ANL' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate
if @tipb='1'
	SET @analitica =1
SET @tipb='0'
--conturi fara miscari
SELECT @tipb=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='CFM' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate
if @tipb='1'
	SET @cfm =1

SET @jurnal= -1
SELECT @tipb=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='JRN' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate
if @tipb<>''
	SELECT @jurnal = IDJurnal FROM [Jurnale] WHERE Codificare=@tipb
 
print(@jurnal)
SELECT @strDataEnd=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='LDT' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate
SET @strDataEnd = RTRIM(LTRIM(@strDataEnd))

SET @tipb='0'
SELECT @tipb=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='TGC' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate
if @tipb='1'
	SET @ptgc =1



IF LEN(@strDataEnd)<>0
BEGIN
	SET @dtDataEndUI = CAST(CAST(@strDataEnd AS float)-2 AS datetime)
	SET @strDataEnd = ''''+CAST(@dtDataEndUI AS varchar(50))+''''
	SET @dataEndFunctie = ''''+CAST(YEAR(@dtDataEndUI) as varchar(4))+'-'+CAST(MONTH(@dtDataEndUI) AS varchar(2))+'-'+CAST(DAY(@dtDataEndUI) AS varchar(2))+''''
	SET @dataStartFunctie = ''''+CAST(YEAR(@dtDataEndUI) as varchar(4))+'-'+CAST(MONTH(@dtDataEndUI) AS varchar(2))+'-01'''
    SET @dtDataStartUI = DATEADD(Day, 1-DAY(@dtDataEndUI),@dtDataEndUI)
END
ELSE
BEGIN
	SET @dataEndFunctie = ''''+CAST(@ultimaData AS varchar(50))+''''
	SET @dataStartFunctie = ''''+CAST(YEAR(@ultimaData) as varchar(4))+'-'+CAST(MONTH(@ultimaData) AS varchar(2))+'-01'''
END


SELECT @strContStart=LTRIM(RTRIM(Valoare)) FROM [ParamListe] WHERE IDLista=@idLista AND Cod='DLC' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate
SELECT @strContEnd=LTRIM(RTRIM(Valoare)) FROM [ParamListe] WHERE IDLista=@idLista AND Cod='PLC' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate
--IF @strContEnd = ''
 -- SET @strContEnd = REPLICATE('Z',50)


DECLARE	@return_value int
PRINT('DECLARE @strContStart as TipCont,@strContEnd as TipCont
DECLARE @strDataStart as varchar(50), @strDataEnd as varchar(50)
DECLARE @tabela varchar(100)
DECLARE @categorie as tinyint
DECLARE @strcategorie as varchar(10)
DECLARE @analitica bit
DECLARE @cfm bit
DECLARE @dataStartFunctie varchar(50)
DECLARE @jurnal int
DECLARE @dataEndFunctie varchar(50)
DECLARE @ultimaData datetime
')
PRINT('SET @DtDataStartUI = ''' + cast(@DtDataStartUI as varchar(20)) + '''')
PRINT('SET @dtDataEndUI=' + cast(@dtDataEndUI as varchar(100)) + '''')
PRINT('SET @jurnal=' + cast(@jurnal as varchar(100)))
PRINT('SET @categorie=' + cast(@categorie as varchar(100)))
PRINT('SET @strContStart=''' + cast(@strContStart as varchar(100)) +'''')
PRINT('SET @StrContEnd =' + cast(@StrContEnd as varchar(100)) + '''')
PRINT('SET @cfm=' + cast(@cfm as varchar(100)))
PRINT('SET @analitica=' + cast(@analitica as varchar(100)))

EXEC	@return_value = [dbo].[GetBalanta]
		@pDtDataStartUI = @DtDataStartUI,
		@pDtDataEndUI = @dtDataEndUI,
		@pJurnal = @jurnal,
		@pCategorie = @categorie,
		@pStrContStart = @strContStart,
		@pStrContEnd = @StrContEnd,
		@pCfm = @cfm,
		@pAnalitica = @analitica,
		@pCfmRulaje = 1,
		@pTerminal = 0,
		@An = @pAn,
		@Tgc=@ptgc


GOTO Gata
EROARE:
	RAISERROR ('Eroare la generarea balantei',16,-1)
Gata:

GO




